在复杂的SQL查询中串联2行 [英] Concatenate 2 rows in a complex SQL query
问题描述
我正在将MS-Access 2003与查询创建者一起使用。我从一个表( FaitsSaillants
)中选择所有内容,然后从另一个表中选择一个特定行( WHERE VARIABLE ='TitreMandat'
)表( tb_SOMMAIRE
)。我想从第二个表中选择另一行并将其连接。
查询
参数
[CurrAxe]文本(255),[CurrOTP]文本(255),
[CurrClient]文本(255),[StartDate] DateTime, [EndDate] DateTime;
选择
tb_SOMMAIRE.Valeur AS Projet,tb_SOMMAIRE.VARIABLE,*
从
(FaitsSaillants
左联接在FaitsSaillants.Utilisateur = Employes.CIP上使用
。 )
内联tb_SOMMAIRE
在FaitsSaillants.OTP = tb_SOMMAIRE.OTP
((((FaitsSaillants.OTP)= [CurrOTP]))和
((FaitsSaillants。 Client)像[CurrClient])和
((FaitsSaillants.Axe)像[CurrAxe])和
((DateValue([DateInsertion])))> = [StartDate] AND
(DateValue ([DateInsertion])< = [EndDate])和
((tb_SOMMAIRE.VARIABLE)='TitreMandat'))
由
订购FaitsSaillants.DateInsertion DESC;
此查询确实添加了 tb_SOMMAIRE.Valeur
ID( OTP
字段)匹配的字段,以及 tb_SOMMAIRE.VARIABLE ='TitreMandat'
的字段。它像一种魅力。但是,我想在 tb_SOMMAIRE
结果中添加另一行。我想获得 VARIABLE ='TitreMandat'
(该部分实际上正在工作)的行和 VARIABLE ='NomInstallation'的行。 code>。当我要求
Projet
( tb_SOMMAIRE.Value作为Projet $ c时,我将得到2行,并希望将这两行连接并显示$ c>)。这两行的
OTP
(ID)与在 FaitsSaillants
中选择的行相同。
对不起,如果它是法文。
表的结构
索引自动编号
Projet文本
斧头文本
客户文本
OTP文本
FaitSaillant备忘录
DateInsertion日期
实用程序文本
tb_SOMMAIRE
OTP文本
可变文本
Valeur文本
数据示例
tb_SOMMAIRE
OTP变量Valeur
UGPSW NomInstallation PosteNemiscau
UGPSW TitreMandat oscilloperturbographe
UGPSW RespIng CU9656
GWIHK NomInstallation
GWIHK TitreMandat任何标题
GWIHK负责任的重要人员
$ c pre>
如何:
参数[CurrAxe] TEXT(255),[CurrOTP] TEXT(255),[CurrClient] TEXT(
255),[StartDate] DATETIME,[EndDate] DATETIME;
选择q.Projet,*
FROM(faitsaillants f
LEFT JOIN使用e
ON f.utilisateur = e.cip)
INNER JOIN(
SELECT s1.otp,
[s1]。[valeur]&,& [s2]。[valeur]作为Projet
FROM(
SELECT otp,valeur
来自tb_sommaire
WHERE [variable] ='TitreMandat')as s1
INNER JOIN(
选择otp,valeur
来自tb_sommaire
WHERE [variable] ='NomInstallation')AS s2
在s1.otp上= s2.otp)q
在f.otp上= q.otp
在哪里f.otp = [currotp]
并且f.client喜欢[currclient]
和f.axe喜欢[curraxe]
AND Datevalue([dateinsertion])
在[startdate]和[enddate]
之间按f排序。 dateinsertion DESC;
始终最好避免将所有字段都引用为*。字段(列)应按名称列出。
以上内容取决于创建一个派生表,该表将Otp对tb_sommaire中的行进行分组。您可以将派生表剪切并粘贴到查询设计屏幕(sql视图)中,以检查返回的行是否符合预期。
I'm using MS-Access 2003 with the query creator. I select everything from one table (FaitsSaillants
), then one specific row (WHERE VARIABLE='TitreMandat'
) from another table (tb_SOMMAIRE
). I want to select another row from that second table and concatenate it.
The query
PARAMETERS
[CurrAxe] Text ( 255 ), [CurrOTP] Text ( 255 ),
[CurrClient] Text ( 255 ), [StartDate] DateTime, [EndDate] DateTime;
SELECT
tb_SOMMAIRE.Valeur AS Projet, tb_SOMMAIRE.VARIABLE, *
FROM
(FaitsSaillants
LEFT JOIN Employes
ON FaitsSaillants.Utilisateur = Employes.CIP)
INNER JOIN tb_SOMMAIRE
ON FaitsSaillants.OTP = tb_SOMMAIRE.OTP
WHERE
(((FaitsSaillants.OTP)=[CurrOTP]) AND
((FaitsSaillants.Client) Like [CurrClient]) AND
((FaitsSaillants.Axe) Like [CurrAxe]) AND
((DateValue([DateInsertion]))>=[StartDate] AND
(DateValue([DateInsertion]))<=[EndDate]) AND
((tb_SOMMAIRE.VARIABLE)='TitreMandat'))
ORDER BY
FaitsSaillants.DateInsertion DESC;
This query does add the tb_SOMMAIRE.Valeur
field where the IDs (OTP
field) match and where tb_SOMMAIRE.VARIABLE='TitreMandat'
. It works like a charm. However, I want to add another row to the tb_SOMMAIRE
results. I would like to get the row where VARIABLE='TitreMandat'
(that part is actually working) and the row where VARIABLE='NomInstallation'
. I will get 2 rows and I want those 2 rows to be concatenated and displayed when I ask for Projet
(tb_SOMMAIRE.Value as Projet
). Both rows' OTP
(IDs) are the same as the one selected in FaitsSaillants
.
Sorry if it's in French.
Tables' structure
FaitsSaillants
Index AutoNumber Projet Text Axe Text Client Text OTP Text FaitSaillant Memo DateInsertion Date Utilisateur Text
tb_SOMMAIRE
OTP Text VARIABLE Text Valeur Text
Data example
tb_SOMMAIRE
OTP VARIABLE Valeur UGPSW NomInstallation PosteNemiscau UGPSW TitreMandat oscilloperturbographe UGPSW RespIng CU9656 GWIHK NomInstallation AnotherInstallation GWIHK TitreMandat Anytitle GWIHK Responsable ImportantPerson
How about:
PARAMETERS [CurrAxe] TEXT ( 255 ), [CurrOTP] TEXT ( 255 ), [CurrClient] TEXT (
255 ), [StartDate] DATETIME, [EndDate] DATETIME;
SELECT q.Projet, *
FROM (faitssaillants f
LEFT JOIN employes e
ON f.utilisateur = e.cip)
INNER JOIN (
SELECT s1.otp,
[s1].[valeur] & "," & [s2].[valeur] AS Projet
FROM (
SELECT otp, valeur
FROM tb_sommaire
WHERE [variable] = 'TitreMandat') AS s1
INNER JOIN (
SELECT otp, valeur
FROM tb_sommaire
WHERE [variable] = 'NomInstallation') AS s2
ON s1.otp = s2.otp) q
ON f.otp = q.otp
WHERE f.otp = [currotp]
AND f.client LIKE [currclient]
AND f.axe LIKE [curraxe]
AND Datevalue([dateinsertion])
Between [startdate] And [enddate]
ORDER BY f.dateinsertion DESC;
It is always best to avoid referencing all fields as *. Fields (columns) should be listed by name.
The above depends on creating a derived table that groups rows from tb_sommaire by Otp. You can cut and paste the derived table into a query design screen (sql view) to check that the rows returned are as expected.
这篇关于在复杂的SQL查询中串联2行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!