帮忙分析这个SQL语句的思想

来源：百度知道 编辑：UC知道 时间：2024/07/09 00:01:36
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (CardID VARCHAR(3),City VARCHAR(2),RegDate DATETIME)
INSERT INTO #T
SELECT '001','wh','2008-01-01 20:00:00' UNION ALL
SELECT '001','wh','2008-01-01 20:01:00' UNION ALL
SELECT '001','wh','2008-01-01 20:02:00' UNION ALL
SELECT '001','wh','2008-01-01 20:03:00' UNION ALL
SELECT '001','wh','2008-01-01 20:04:00' UNION ALL
SELECT '001','sz','2008-01-01 20:05:00' UNION ALL
SELECT '001','sz','2008-01-01 20:06:00' UNION ALL
SELECT '001','wh','2008-01-01 20:07:00' UNION ALL
SELECT '001','wh','2008-01-01 20:08:00' UNION ALL
SELECT '001','wh','2008-01-01 20:09:00' UNION ALL
SELECT '001','hz','2008-01-

001 wh 2008-01-01 20:00:00.000 0
001 wh 2008-01-01 20:01:00.000 0
001 wh 2008-01-01 20:02:00.000 0
001 wh 2008-01-01 20:03:00.000 0
001 wh 2008-01-01 20:04:00.000 0
001 sz 2008-01-01 20:05:00.000 5
001 sz 2008-01-01 20:06:00.000 5
001 wh 2008-01-01 20:07:00.000 2
001 wh 2008-01-01 20:08:00.000 2
001 wh 2008-01-01 20:09:00.000 2
001 hz 2008-01-01 20:10:00.000 10
001 hz 2008-01-01 20:11:00.000 10
001 hz 2008-01-01 20:12:00.000 10

执行这段得到上面的数据
SELECT *,
Row=(SELECT COUNT(*)
FROM #T
WHERE CardID=T.CardID
AND RegDate<T.RegDate
AND City<>T.City)
FROM #T AS T
或(也可以用下面的写法)
SELECT *,
(SELECT COUNT(*)
FROM #T
WHERE CardID=T.CardID
AND RegDate<T.RegDate
AND City<>T.City) as Row
FROM #T AS T
分析得:用映象表T 和 #T比较 得出
该条件下
CardID=T.CardID
AND RegDate<T.RegDate
AND City<>T.City
的row（行数)