27,579
社区成员
发帖
与我相关
我的任务
分享
--#1.拼字段串的话,'号需要转义成''
--#2.access不支持CTE,且不支持ROW_NUMBER函数,建议增加自增ID,且用只能用字查询实现
--#3.加个PARTITION BY keyjobno即可
;WITH
cte1 AS
(
SELECT *, rowid=ROW_NUMBER() OVER(PARTITION BY keyjobno ORDER BY GETDATE()) FROM A表
),
cte2 AS
(
SELECT *, rowid=ROW_NUMBER() OVER(PARTITION BY keyjobno ORDER BY GETDATE()) FROM B表
),
cte3 AS
(
SELECT *, rowid=ROW_NUMBER() OVER(PARTITION BY keyjobno ORDER BY GETDATE()) FROM C表
)
SELECT
keyjobno = (CASE a.rowid WHEN 1 THEN a.keyjobno ELSE '' end),
[name] = (CASE a.rowid WHEN 1 THEN a.[name] ELSE '' end),
a.marry,a.bear,a.other,b.indate,c.activity,c.[date]
FROM cte1 a
INNER JOIN cte2 b
ON a.keyjobno = b.keyjobno
AND a.[name] = b.[name]
AND a.rowid = b.rowid
INNER JOIN cte3 c
ON a.keyjobno = c.keyjobno
AND a.[name] = c.[name]
AND a.rowid = c.rowid
--access用IIF(条件,结果1,结果2),条件为true则取结果1,否则取结果2
--应该是你Delphi端取值方法不对。ADO的话,应该不会出现这种问题。请把你的相关代码,放到Delphi区去问问
--你这个有问题,没说明A,B,C三个表之间的记录的关联关系。
;WITH
cte1 AS
(
SELECT *, rowid=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM A表
),
cte2 AS
(
SELECT *, rowid=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM B表
),
cte3 AS
(
SELECT *, rowid=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM C表
)
SELECT
keyjobno = (CASE a.rowid WHEN 1 THEN a.keyjobno ELSE '' end),
[name] = (CASE a.rowid WHEN 1 THEN a.[name] ELSE '' end),
a.marry,a.bear,a.other,b.indate,c.activity,c.[date]
FROM cte1 a
INNER JOIN cte2 b
ON a.keyjobno = b.keyjobno
AND a.[name] = b.[name]
AND a.rowid = b.rowid
INNER JOIN cte3 c
ON a.keyjobno = c.keyjobno
AND a.[name] = c.[name]
AND a.rowid = c.rowid