gpt4 book ai didi

sql - 从SQL中的表中获取单个数据?

转载 作者:行者123 更新时间:2023-12-04 21:08:18 25 4
gpt4 key购买 nike

我的SQL Server中有三个表

1.)注册它的列

   Reg_Id     bigint, primary key and auto increment
Name nvarchar(50),
Tranx_id nvarchar(30),
Email nvarchar(30),
Username nvarchar(30),
Password nvarchar(30),
Edition_Id nvarchar(50),
Default_Id nvarchar(50),
Reg_Date datetime,
usertype nvarchar(50),


2.)AllEditionPages它是列

Page_id       bigint, primary key and auto increment
edition_date datetime,
noofpages int,
Page_no int,
image_path nvarchar(50),
Active int,
type_of_page varchar(50),
Image_Width int,
Image_Height int,


3.)版

id  int, primary key and auto increment
edition_date datetime,
noofpages int,
XMLFile nvarchar(50),
PDFFile nvarchar(50),
PDFPrefix nvarchar(50),
type nvarchar(50),
price nvarchar(50),
reg_req nvarchar(50)


根据上表我尝试下面的SQL查询

    SELECT edi.*, aep.*, reg.*
FROM Edition as edi INNER JOIN Registration as reg
ON edi.id = reg.Edition_Id INNER JOIN AllEditionPages as aep
ON edi.edition_date = aep.edition_date
where reg.Edition_Id= edi.id
and reg.Reg_ID = 14


从此查询中,我得到以下输出:

id    edition_date              type    price  page-id

96 2012-07-18 00:00:00.000 free null 2503
96 2012-07-18 00:00:00.000 free null 2503


我在输出中得到两行,但只希望输出中有一行

最佳答案

在这种情况下,您可以通过以下方式使用common table expression

 WITH cteTable AS
(
SELECT edi.*, aep.*, reg.*
FROM Edition as edi INNER JOIN Registration as reg
ON edi.id = reg.Edition_Id INNER JOIN AllEditionPages as aep
ON edi.edition_date = aep.edition_date
where reg.Edition_Id= edi.id
and reg.Reg_ID = 14
)
select top 1 * from cteTable


当两个表包含edition_date时,您需要使用以下方式来区分列名称而不是使用“ *”:

WITH cteTable AS
(
SELECT edi.id,edi.edition_date,edi.noofpagez,edi.XMLFile,edi.PDFFile ,edi.PDFPrefix,edi.type,edi.price,edi.reg_req ,
aep.Page_id, aep.edition_date,aep.noofpages,aep.Page_no,aep.image_path,aep.Active,aep.type_of_page,aep.Image_Width,aep.Image_Height,
reg.Reg_Id,reg.Name,reg.Tranx_id,reg.Email,reg.Username,reg.Password,reg.Edition_Id,reg.Default_Id,reg.Reg_Date,reg.usertype
FROM Edition as edi INNER JOIN Registration as reg
ON edi.id = reg.Edition_Id INNER JOIN AllEditionPages as aep
ON edi.edition_date = aep.edition_date
where reg.Edition_Id= edi.id
and reg.Reg_ID = 14
)
select top 1 * from cteTable

关于sql - 从SQL中的表中获取单个数据?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/11591715/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com