SELECT COUNT(DISTINCT I5 ),COUNT(DISTINCT I6),COUNT(DISTINCT I7),COUNT(DISTINCT I8),COUNT(DISTINCT I9),COUNT(DISTINCT I10) FROM ( SELECT CASE WHEN TableTypeID = 5 THEN CellID ELSE NULL END AS I5, CASE WHEN TableTypeID = 6 THEN CellID ELSE NULL END AS I6, CASE WHEN TableTypeID = 7 THEN CellID ELSE NULL END AS I7, CASE WHEN TableTypeID = 8 THEN CellID ELSE NULL END AS I8, CASE WHEN TableTypeID = 9 THEN CellID ELSE NULL END AS I9, CASE WHEN TableTypeID = 10 THEN CellID ELSE NULL END AS I10 FROM vw_Finance_Statistics WHERE CompanyID =33222 AND UserTableID is NULL ) T
--试试: SELECT * FROM ( SELECT TableTypeID, cnt = COUNT(DISTINCT CellID) FROM vw_Finance_Statistics WHERE CompanyID = 33222 AND UserTableID IS NULL AND TableTypeID IN (5, 6, 7, 8, 9, 10) GROUP BY TableTypeID ) A PIVOT (MAX(cnt) FOR TableTypeID IN([5], [6], [7], [8], [9], [10])) B --CellID字段加索引.
try~SELECT [5] AS '5',[6] AS '6',[7] AS '7',[8] AS '8',[9] AS '9',[10] AS '10' FROM ( SELECT DISTINCT CellID, TableTypeID FROM vw_Finance_Statistics WHERE CompanyID = 33222 AND UserTableID is NULL ) a PIVOT ( COUNT(CellID) FOR TableTypeID IN ([5],[6],[7],[8],[9],[10])) pvt
SELECT COUNT(DISTINCT I5 ),COUNT(DISTINCT I6),COUNT(DISTINCT I7),COUNT(DISTINCT I8),COUNT(DISTINCT I9),COUNT(DISTINCT I10)
FROM (
SELECT CASE WHEN TableTypeID = 5 THEN CellID ELSE NULL END AS I5,
CASE WHEN TableTypeID = 6 THEN CellID ELSE NULL END AS I6,
CASE WHEN TableTypeID = 7 THEN CellID ELSE NULL END AS I7,
CASE WHEN TableTypeID = 8 THEN CellID ELSE NULL END AS I8,
CASE WHEN TableTypeID = 9 THEN CellID ELSE NULL END AS I9,
CASE WHEN TableTypeID = 10 THEN CellID ELSE NULL END AS I10
FROM vw_Finance_Statistics WHERE CompanyID =33222 AND UserTableID is NULL
) T
SELECT * FROM
(
SELECT
TableTypeID,
cnt = COUNT(DISTINCT CellID)
FROM vw_Finance_Statistics
WHERE CompanyID = 33222
AND UserTableID IS NULL
AND TableTypeID IN (5, 6, 7, 8, 9, 10)
GROUP BY TableTypeID
) A
PIVOT
(MAX(cnt) FOR TableTypeID IN([5], [6], [7], [8], [9], [10])) B
--CellID字段加索引.
SELECT DISTINCT CellID, TableTypeID FROM vw_Finance_Statistics
WHERE CompanyID = 33222 AND UserTableID is NULL
) a
PIVOT (
COUNT(CellID) FOR TableTypeID IN ([5],[6],[7],[8],[9],[10])) pvt