參考：
http://www.programbbs.com/bbs/view25-12320-1.htm但上面的 一些sql語(yǔ)句 ，有查詢時(shí)間問(wèn)題，在此我已于改正了并希望和大家分享。
---------自我感覺(jué) 第四題 很不錯(cuò)！-----------------------------------------------------------------------------------
第一題(只要重復(fù)的都要?輸出)
----------------------------------------------------------------------
create?table?c?(id?int?);
insert?into?c?values?(1),(2),(3),(4),(3),(5),(6),(1);
結(jié)果:
????select?id?from?c?group?by?id?having?count(id)>1?;
附加（查詢重復(fù)最多的倒排）
select?bid,count(bid)?as?cu?from?tb?group?by?bid?having?count(bid)>1??order?by?cu??desc----------------------------------------------------------------------
第二題(報(bào)表查詢橫向輸出)
查詢后的結(jié)果：
Create table d(id int,name varchar(50));
insert into d values(1,'gly');
insert into d values(2,'ptgly');
insert into d values(3,'ybgly');
insert into d values(4,'ptgly');
insert into d values(5,'ybgly');
+---+-----+------+
|gly|ptgly|ybgly | ????
|---+-----+------+-
|1? |2??? |2???? |
+---+-----+------+
-----------------------------------------------------------------------
select?
????sum(case?when?name='gly'?then?1?else?0?end?)?as?gly??,
????sum(case?when?name='ptgly'?then?1?else?0?end?)?as?ptgly??,
????sum(case?when?name='ybgly'?then?1?else?0?end?)?as?ybgly??
from?d?;
第三題
可以根據(jù)上面 2,4 可以得出 。
下面我們?cè)敿?xì)說(shuō)下 4題。
-----------------------------------------------------------------------
第四題（復(fù)雜組合查詢！）
create table table_a (No int, No2 int,num double,itime date);
insert into table_a values
???? (1234,567890,33.5,'2004-12-21'),
???? (1234,598701,44.8,'2004-11-21'),
???? (1234,598701,45.2,'2004-10-01'),
???? (1234,567890,66.5,'2004-9-21'),
???? (3456,789065,22.5,'2004-10-01'),
???? (3456,789065,77.5,'2004-10-27'),
???? (3456,678901,48.5,'2004-12-21');
按月統(tǒng)計(jì)銷售表中貨物的銷售量數(shù)
查詢結(jié)果如下：
? No， No2 ，?? 九月， 十月，十一月，十二月
1234，567890， 66.5 ， 0 ， 0? ，? 33.5
1234，598701，? 0?? ， 45.2， 44.8， 0
3456，789065， 0? ， 100， 0? ， 0
3456，678901， 0 ，??? 0，??? 0? ，? 48.5
-----------------------------------------------------------------------
//當(dāng)然也可以 使用mysql 時(shí)間函數(shù) 在軟件編輯時(shí) 你可以輸入 String[] 并根據(jù)數(shù)據(jù)動(dòng)態(tài)拼寫 sql( case部分！！ )
//這個(gè) 例子很好 哦！報(bào)表可以一句sql 得出！
select?NO,NO2,
????sum(case??when?itime?like?'2004-%9%'?then?num?else?0?end)?as?9M,
????sum(case??when?itime?like?'2004-10%'?then?num?else?0?end)?as?10M,
????sum(case??when?itime?like?'2004-11%'?then?num?else?0?end)?as?11M,
????sum(case??when?itime?like?'2004-12%'?then?num?else?0?end)?as?12M
from?table_a?group?by?no,no2?order?by?no,no2?;
--------------------------------------------------------------
第五題
代碼,名稱
1???? a
2???? b
11??? c
(代碼11表示為1的下級(jí))
我要通過(guò)一條句子,得出如下結(jié)果:
代碼,名稱,有無(wú)下級(jí)
1???? a???? 有
2???? b???? 無(wú)
11??? c???? 無(wú)
Create table TabTest(t_Code varchar(10),t_Name varchar(10));
insert into TabTest values('1','a');
insert into TabTest values('2','b');
insert into TabTest values('11','c');
--------------------------------------------------------------
select?tt1.t_Code,tt1.t_name,(?
?????case?
?????????????when?exists?(select?1?from?tabtest?tt2?
??????????????????????????????????????? where?tt2.t_code?like?CONCAT(tt1.t_code,'%')?and?
??????????????????????????????????????? tt2.t_code?<>?tt1.t_code?)??then?'you'?
?????????????else?'wu'
??????end?)?as?you_wu
from?tabtest??tt1?;