sql 使用心得
使用的创建数据库脚本如下
1 CREATE DATABASE [aa] 2 3 CREATE TABLE [dbo].[news]( 4 [gameTime] [nvarchar](50) NULL, 5 [result] [nvarchar](10) NULL, 6 [ID] [int] IDENTITY(1,1) NOT NULL, 7 CONSTRAINT [PK_news] PRIMARY KEY CLUSTERED 8 ( 9 [ID] ASC 10 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 11 ) ON [PRIMARY] 12 13 14 INSERT [dbo].[news] ([gameTime], [result], [ID]) VALUES (N'2011-05-09', N'胜', 1) 15 INSERT [dbo].[news] ([gameTime], [result], [ID]) VALUES (N'2011-05-09', N'胜', 2) 16 INSERT [dbo].[news] ([gameTime], [result], [ID]) VALUES (N'2011-05-09', N'负', 3) 17 INSERT [dbo].[news] ([gameTime], [result], [ID]) VALUES (N'2011-05-09', N'负', 4) 18 INSERT [dbo].[news] ([gameTime], [result], [ID]) VALUES (N'2011-05-10', N'胜', 5) 19 INSERT [dbo].[news] ([gameTime], [result], [ID]) VALUES (N'2011-05-10', N'负', 6) 20 INSERT [dbo].[news] ([gameTime], [result], [ID]) VALUES (N'2011-05-10', N'负', 7)
1、用SQL查询一批数据，怎么在最后一行增加个数字合计
使用WITH ROLLUP 来统计数据,增加一行
SELECT CASE WHEN gameTime IS NULL THEN '合计' ELSE gameTime END AS gameTime, -------如果为NULL 则显示合计 sum(CASE WHEN result='胜'THEN 1 ELSE 0 end ) AS 胜, -------统计胜的数量 sum(CASE WHEN result='负' THEN 1 ELSE 0 end)AS 负 FROM dbo.news GROUP BY gameTime WITH ROLLUP
使用union all 来统计数据,增加一行
SELECT gameTime, sum(CASE WHEN result='胜'THEN 1 ELSE 0 end ) AS 胜, sum(CASE WHEN result='负' THEN 1 ELSE 0 end)AS 负 FROM dbo.news GROUP BY gameTime UNION ALL ---------合并数据 SELECT '合计',sum(胜),sum(负) FROM (SELECT gametime, sum(CASE WHEN result='胜'THEN 1 ELSE 0 end ) AS 胜, sum(CASE WHEN result='负' THEN 1 ELSE 0 end)AS 负 FROM dbo.news GROUP BY gameTime) AS cc
