在三個表中，其中第一個表  回復  更多評論
  

抱歉！剛才測試一下本網站是否好用！現在出個讓大家討論的sql語句

a b c 三個字段有可能重復







數據如下:







a b c d e



---------- ---------- ---------- ----------- -----------



a a a 1 2



a a a 1 2



a a a 1 2



a a a 1 2



a a a 1 2



b b b 3 3



b b b 3 3



b b b 3 3



b b b 3 3



b b b 3 3



b b b 3 3



b b b 3 3



c c c 4 4



d d d 5 5



d d d 5 5







問題一: 現在要把三個字段有重復的數據提出來.



問題二: 合并表中的數據,比如



a a a 1 2



a a a 1 2



a a a 1 2



a a a 1 2



a a a 1 2



這五條紀錄要合并成一條



a a a 5 10


  回復  更多評論
  

select t.name
from
(
select max(購物人) as name,count(購物人) as cnt
from chanpin
group by 商品名稱
) t
where t.cnt >=2  回復  更多評論
  

select t.name,t.cnt from(
select userName as name,count(userName) as cnt
from exam1
group by userName)t
where t.cnt>=2  回復  更多評論
  

+---------+---------+----------+
| cp_name | cp_kind | cp_count |
+---------+---------+----------+
| A | a | 1 |
| A | c | 2 |
| A | t | 1 |
| B | t | 1 |
| B | a | 2 |
| C | d | 1 |
| D | c | 2 |
| C | d | 3 |
+---------+---------+----------+
上面兩道答案明顯錯誤，題目的意思是返回購買“兩種或兩種商品以上“的客戶信息。
正確答案應該是：
select t.cp_name,t.cnt from (select cp_name,count(distinct cp_kind) as cnt from chanpin group by cp_name) t where t.cnt>=2;
  回復  更多評論
  

@暢暢暢
select * from T1
where customer_name in
(select customer_name
from T1 as b
group by customer_name
having count(distinct trade_name)>=2)  回復  更多評論
  

select * from sales as t1 where exists
(
select t2.customerName from sales as t2 where t1.customerName = t2.customerName
group by t2.customerName
having (count(t2.customerName) >= 2)
)  回復  更多評論
  

@暢暢暢
這個不是一句就夠了么。
select cp_name,count(distinct cp_kind) cnt from tableName group by cp_name having cnt >= 2;  回復  更多評論
  

tableName:test
person gName scount
A 甲 2
B 乙 4
C 丙 1
A 丁 2
B 丙 5
給出所有購入商品為兩種或兩種以上的購物人記錄

select person,count(gName) from test GROUP BY person having count(gName)>=2

select * from (select person,count(gName) as gc from test GROUP BY person) as a where a.gc>=2   回復  更多評論
  

樓上回答的第一題都是錯的。如果在加數據肯定不行。下邊我把正確的寫一下
注意了：第一題
select s.ren from (select distinct(shangpin), ren from tt group by ren , shangpin) s group by s.ren having count(s.ren)>=2
  回復  更多評論
  

For SQL SERVER:
declare @t3 table (buyer nvarchar(50),goods nvarchar(50),qty int)
insert into @t3 values('A','甲',2)
insert into @t3 values('A','甲',3)
insert into @t3 values('B','乙',4)
insert into @t3 values('C','丙',1)
insert into @t3 values('C','丙',2)
insert into @t3 values('A','丁',2)
insert into @t3 values('B','丙',5)
select * from @t3

select t1.buyer,t1.goods,SUM(t1.qty) from @t3 t1 inner join @t3 t2 on t1.buyer=t2.buyer and t1.goods <> t2.goods
group by t1.buyer,t1.goods  回復  更多評論