sql server2005练习题
1、CREATE table tt (a int , b int , c int , d int , e int , f int, g int)
insert tt select 1,2,3,14,5,6,7
insert tt select 11,4,9,4,12,26,7
insert tt select 1,12,4,4,10,16,9
--表中共有a-g七列，a-g列的标准值分别为：1,2,3,4,5,6,7. 要求只要任意三列
--的值等于标准值即为符合要求行，要求给出能搜出所有符合要求行的SQL语句。
--上表中行1,2,3,14,5,6,7即为符合要求行。
解：
CREATE TABLE th
(a int , b int , c int , d int , e int , f int, g int)
INSERT INTO th
SELECT 1 ,2,3,4,5,6,7
GO
SELECT * FROM tt WHERE ((CASE tt.a WHEN 1 THEN 1 ELSE 0 END )+
(CASE tt.b WHEN 2 THEN 1 ELSE 0 END )+
(CASE tt.c WHEN 3 THEN 1 ELSE 0 END )+
(CASE tt.d WHEN 4 THEN 1 ELSE 0 END )+
(CASE tt.e WHEN 5 THEN 1 ELSE 0 END )+
(CASE tt.f WHEN 6 THEN 1 ELSE 0 END ) +
(CASE tt.g WHEN 7 THEN 1 ELSE 0 END ))>3;
2、--sql语句分类汇总
--生成一个表
CREATE TABLE ta (仓库名称 VARCHAR(9),商品编码 VARCHAR(10),商品名称 VARCHAR(20),库存 INT) ;
GO
INSERT INTO ta
SELECT '上海仓库','07-c01-000','NOKIA诺基亚C',122 UNION ALL
SELECT '上海仓库','07-c01-098','SonyEricsson索爱W380',244 UNION ALL
SELECT '广州仓库','07-c01-098','SonyEricsson索爱W380',355 UNION ALL
SELECT '成都仓库','07-c01-026','Nokia诺基亚N72',58 UNION ALL
SELECT '广州仓库','07-c01-026','Nokia诺基亚N72',62 UNION ALL
SELECT '成都仓库','07-c01-098','SonyEricsson索爱W380',88 UNION ALL
SELECT '成都仓库','07-c01-000','NOKIA诺基亚C',30
GO
--
--要求写一条SQL查询语句，得到如下汇总结果：
--仓库名称 商品编码 商品名称 库存
----------- ---------- -------------------- -----------
--上海仓库 07-c01-000 NOKIA诺基亚C 122
--成都仓库 07-c01-000 NOKIA诺基亚C 30
--总库存: 152
--成都仓库 07-c01-026 Nokia诺基亚N72 58
--广州仓库 07-c01-026 Nokia诺基亚N72 62
--总库存: 120
--成都仓库 07-c01-098 SonyEricsson索爱W380 88
--上海仓库 07-c01-098 SonyEricsson索爱W380 244
--广州仓库 07-c01-098 SonyEricsson索爱W380 355
--总库存: 687
解：
一条sql语句写出，我现在有点思路，但是还没有写出来。看网上对于这种形式的题型好像有几种做法。我先列出我自己的先。不尽完美的是我是用分开的语句写的，没有完成上面的要求。
--生成另外一个表
CREATE TABLE tai (仓库名称 VARCHAR(9),商品编码 VARCHAR(10),商品名称 VARCHAR(20),库存 INT) ;
GO
--将with rollup的信息插入tai中
INSERT INTO tai SELECT 仓库名称,商品编码,商品名称 ,SUM(库存) AS 库存 FROM TA
GROUP BY 商品编码,仓库名称,商品名称 WITH rollup;
GO
--对tai进行操作： 删掉多余行
DELETE FROM tai WHERE (仓库名称 IS NOT NULL) AND ( 商品名称 IS NULL) AND (商品编码 IS NOT NULL) ;
GO
--对tai的必要项的值进行修改
UPDATE tai SET 仓库名称='总库存' WHERE 仓库名称 IS NULL;
GO
UPDATE tai SET 商品编码='' WHERE 仓库名称='总库存';
GO
UPDATE tai SET 商品名称='' WHERE 商品名称 IS NULL ;
GO
--最后输出汇总图表
SELECT * FROM tai ;
GO
--最后可以把它连起来可以生成一个语句就可以啦，或者生成存储过程
上面的思路算是比较清晰的了。网上另有些方法，不过思路跟上面的过程差不多，这里提一下这个模型：
用Grouping替换NULL值
SELECT CASE WHEN (GROUPING(编码) = 1) THEN 'ALL'
ELSE ISNULL(编码, 'UNKNOWN')
END AS 编码,
CASE WHEN (GROUPING(仓库) = 1) THEN 'ALL'
ELSE ISNULL(仓库, 'UNKNOWN')
END AS 仓库,
SUM(数量) AS 数量
FROM A
GROUP BY 编码, 仓库 WITH ROLLUP
--适当的运用Case函数
等有了更好的方法，再提供。
3、CREATE TABLE ti (TypeId INT,TypeName VARCHAR(10),TypeParentID INT)
INSERT ti SELECT 2, '外语', NULL
UNION ALL SELECT 17, '英语', 2
UNION ALL SELECT 18,'法语' ,2
UNION ALL SELECT 19, '德语' , 2
UNION ALL SELECT 1, '计算机' ,NULL
UNION ALL SELECT 8, '编程' ,1
GO
--要求写一条SQL查询语句，得到如下汇总结果：
--TypeID TypeName ParentTypeName
------------- ---------- --------------
--17 英语 外语
--18 法语 外语
--19 德语 外语
--8 编程 计算机
解：
SELECT aaa.TypeId,aaa.TypeName,bbb.typename
FROM (SELECT TYPEid,typename,typeparentid FROM ti WHERE typeparentid IS NOT NULL) AS aaa
,(SELECT TYPEid,typename FROM ti WHERE typeparentid IS NULL) AS bbb
WHERE aaa.TypeParentID=bbb.typeid;
