22,210
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE (
item0 NVARCHAR(5),
item1 NVARCHAR(5),
item2 NVARCHAR(5)
)
INSERT INTO @t
SELECT '合格','不合格','合格'
UNION ALL SELECT '合格','合格','合格'
UNION ALL SELECT '不合格','合格','不合格'
UNION ALL SELECT '不合格','不合格','合格'
DECLARE @t2 TABLE (
item0 NVARCHAR(5),
item1 NVARCHAR(5),
item2 NVARCHAR(5)
)
INSERT INTO @t2
SELECT '不合格','合格','不合格'
UNION ALL SELECT '合格','合格','合格'
UNION ALL SELECT '不合格','合格','不合格'
UNION ALL SELECT '不合格','不合格','合格'
SELECT
SUM(itme0合格数) AS itme0合格数
,SUM(itme0不合格数) AS itme0合格数
,SUM(itme1合格数) AS itme1合格数
,SUM(itme1不合格数) AS itme1合格数
FROM (
SELECT
SUM(CASE WHEN item0='合格' THEN 1 ELSE 0 END) AS itme0合格数
,SUM(CASE WHEN item0='合格' THEN 0 ELSE 1 END) AS itme0不合格数
,0 AS itme1合格数
,0 AS itme1不合格数
FROM @t
UNION ALL
SELECT
0 AS itme0合格数
,0 AS itme0不合格数
,SUM(CASE WHEN item1='合格' THEN 1 ELSE 0 END) AS itme1合格数
,SUM(CASE WHEN item1='合格' THEN 0 ELSE 1 END) AS itme1不合格数
FROM @t2
) AS t
/*
itme0合格数 itme0不合格数 itme1合格数 itme1不合格数
2 2 3 1
*/
这是 SQL Server 中能运行的, 你按类似的思路在Access 中试试。SELECT
SUM(CASE WHEN item0='合格' THEN 1 ELSE 0 END) AS itme0合格数
,SUM(CASE WHEN item0='合格' THEN 0 ELSE 1 END) AS itme0不合格数
,SUM(CASE WHEN item1='合格' THEN 1 ELSE 0 END) AS itme1合格数
,SUM(CASE WHEN item1='合格' THEN 0 ELSE 1 END) AS itme1不合格数
,SUM(CASE WHEN item2='合格' THEN 1 ELSE 0 END) AS itme2合格数
,SUM(CASE WHEN item2='合格' THEN 0 ELSE 1 END) AS itme2不合格数
FROM t
DECLARE @t TABLE (
item0 NVARCHAR(5),
item1 NVARCHAR(5),
item2 NVARCHAR(5)
)
INSERT INTO @t
SELECT '合格','不合格','合格'
UNION ALL SELECT '合格','合格','合格'
UNION ALL SELECT '不合格','合格','不合格'
UNION ALL SELECT '不合格','不合格','合格'
SELECT
SUM(CASE WHEN item0='合格' THEN 1 ELSE 0 END) AS itme0合格数
,SUM(CASE WHEN item0='合格' THEN 0 ELSE 1 END) AS itme0不合格数
,SUM(CASE WHEN item1='合格' THEN 1 ELSE 0 END) AS itme1合格数
,SUM(CASE WHEN item1='合格' THEN 0 ELSE 1 END) AS itme1不合格数
,SUM(CASE WHEN item2='合格' THEN 1 ELSE 0 END) AS itme2合格数
,SUM(CASE WHEN item2='合格' THEN 0 ELSE 1 END) AS itme2不合格数
FROM @t
/*
itme0合格数 itme0不合格数 itme1合格数 itme1不合格数 itme2合格数 itme2不合格数
2 2 2 2 3 1
*/