SQL case 语句怎么用 - 编程论坛

lff6422008-04-25 14:54

试一下

create table tb (UNO int,XM varchar(20),xb char(2))

insert into tb select 1,'tt','男'
union all select  2,'tt2','男'
union all select  3,'tt3','女'
union all select 4,'tt4','女'

select '男生总数' = sum(case when xb='男' then 1 else 0 end),
        '女生总数'= sum(case when xb='女' then 1 else 0 end)
from tb

_eagle_ice2008-05-15 21:49

case

SELECT   Category =
      CASE type
         WHEN 'popular_comp' THEN 'Popular Computing'
         WHEN 'mod_cook' THEN 'Modern Cooking'
         WHEN 'business' THEN 'Business'
         WHEN 'psychology' THEN 'Psychology'
         WHEN 'trad_cook' THEN 'Traditional Cooking'
         ELSE 'Not yet categorized'
      END,
   CAST(title AS varchar(25)) AS 'Shortened Title',
   price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO