A. sql 查詢 一個表中有科目、名字、狀態三個欄位, 怎麼輸出下面的結果（每個狀態的人數）
select 科目, sum(case 狀態 when '通過'then 1 else 0 end ) 通過,
sum(case 狀態 when '不通過' then 1 else 0 end ) 不通過 ,
sum (case 狀態 when '待定' then 1 else 0 end ) 待定
from table
group by 科目
B. SQL中查詢有三個欄位的記錄相同的語句
select a.ID,a.NAME,a.SPC,a.UT from(select * from PRDT) as a,(select * from PRDT) as bwhere a.NAME=b.NAME and a.SPC=b.SPC and a.UT=b.UT and a.ID<>b.ID order by a.ID試試~
C. SQL Server 在查詢結果增加自定義的三個欄位如何搞
在後面加上如下語句：
(selectsum(sh.Precipitation)fromStationAwsh1f
wheref.StationId=sh.StationId
andf.ObservTimesBetweensh.ObservTimesand
replace(replace(convert(varchar(13),dateadd(hh,12,cast(left(sh.ObservTimes,8)+''+right(sh.ObservTimes,2)+':00'asdatetime)),120),'-',''),'','')
)asPre12H,
(selectsum(sh.Precipitation)fromStationAwsh1f
wheref.StationId=sh.StationId
andf.ObservTimesBetweensh.ObservTimesand
replace(replace(convert(varchar(13),dateadd(hh,24,cast(left(sh.ObservTimes,8)+''+right(sh.ObservTimes,2)+':00'asdatetime)),120),'-',''),'','')
)asPre24H,
(selectsum(sh.Precipitation)fromStationAwsh1f
wheref.StationId=sh.StationId
andf.ObservTimesBetweensh.ObservTimesand
replace(replace(convert(varchar(13),dateadd(hh,72,cast(left(sh.ObservTimes,8)+''+right(sh.ObservTimes,2)+':00'asdatetime)),120),'-',''),'','')
)asPre72H
D. 如何用SQL語句把現有3個表中的3個欄位的所有組合方式創建一張新表
select sum(PICTURE)
from (
select count(PICTURE)*5 PICTURE from "DB2ADMIN"."C_NEWS" where PICTURE is not null
union all
select count(*)*5 DETAILS from "DB2ADMIN"."C_NEWS" where length(DETAILS)<1000 and="">0
union all
select count(*)*10 DETAILS from "DB2ADMIN"."C_NEWS" where length(DETAILS)>1000
) a