有没有更好的方法来做这个查询?
这是我的sql,但我认为它太复杂了,也许它不是非常快速和高效?我对吗?或者,还有更好的方法?有没有更好的方法来做这个查询?
SELECT TEventInfoid ,A.TTNum,
CASE WHEN TModelid = 3822 THEN (SELECT title
FROM dbo.sns_event WHERE id=TEventInfoid
)
ELSE (SELECT name
FROM dbo.sns_exhibition WHERE id=TEventInfoid
)
END,
CASE WHEN TModelid = 3822 THEN (SELECT start_time
FROM dbo.sns_event WHERE id=TEventInfoid
)
ELSE (SELECT start_Time
FROM dbo.sns_exhibition WHERE id=TEventInfoid
)
END,
CASE WHEN TModelid = 3822 THEN (SELECT [address]
FROM dbo.sns_event WHERE id=TEventInfoid
)
ELSE (SELECT [address]
FROM dbo.sns_exhibition WHERE id=TEventInfoid
)
END
FROM
T_Ticktets A
WHERE A.TTNum='100229143644'
使用LEFT JOIN
:
SELECT t.TEventInfoid, t.TTNum,
(CASE WHEN t.TModelid = 3822 THEN e.title ELSE ex.name END),
(CASE WHEN t.TModelid = 3822 THEN e.start_time ELSE ex.start_time END),
(CASE WHEN t.TModelid = 3822 THEN e.address ELSE ex.address END)
FROM T_Ticktets t LEFT JOIN
dbo.sns_event e
ON e.id = t.TEventInfoid LEFT JOIN -- unclear which table which is in
dbo.sns_exhibition ex
ON ex.id = t.TEventInfoid AND
e.id IS NULL -- unclear which table which is in
WHERE t.TTNum = '100229143644';
注:join
条件可能有错误的表。目前还不清楚哪个列在哪个表中。
另外,如果被声明为一个大整数或数字,则可以在与之比较的值周围放置单引号。
此版本确实做出了一个假设。它假定对于给定的id
,其他表中没有多行。如果是这样的话,调整查询不会太困难。
编辑:
修理好了最后一个问题的办法很简单:
SELECT t.TEventInfoid, t.TTNum,
COALESCE(e.title, ex.name),
COALESCE(e.start_time, ex.start_time),
COALESCE(e.address, ex.address)
FROM T_Ticktets t LEFT JOIN
dbo.sns_event e
ON e.id = t.TEventInfoid AND t.TModelid = 3822 LEFT JOIN -- unclear which table which is in
dbo.sns_exhibition ex
ON ex.id = t.TEventInfoid AND t.TModelid <> 3822 -- unclear which table which is in
WHERE t.TTNum = '100229143644';
这可能是用于查询的最佳形式。注意:如果TModelid
可能是NULL
,则必须在第二个ON
条件中考虑这一点。
您可以使用它。
SELECT
TEventInfoid ,A.TTNum,
CASE WHEN TModelid = 3822 THEN e.title ELSE x.name END,
CASE WHEN TModelid = 3822 THEN e.start_time ELSE x.start_Time END,
CASE WHEN TModelid = 3822 THEN e.[address] ELSE x.[address] END
FROM
T_Ticktets A
LEFT JOIN dbo.sns_event e ON e.id=A.TEventInfoid
LEFT JOIN dbo.sns_exhibition x WHERE x.id=A.TEventInfoid
WHERE A.TTNum='100229143644'
3Q,我觉得它更舒适 – jasonLam
你会如何对自己说这个?我希望所有这里的门票模型ID是3822,所有展品的事件,它是不是3822
SELECT A.TEventInfoid,
A.TTNum,
e.Title,
e.start_time,
e.[address],
A.TModelid
FROM T_Ticktets A
JOIN dbo.sns_event e
ON A.id = e.TEventInfoid
WHERE A.TTNum='100229143644'
AND A.TModelid = 3822
UNION ALL
SELECT A.TEventInfoid,
A.TTNum,
e.Name
e.start_time,
e.[address],
A.TModelid
FROM T_Ticktets A
JOIN dbo.sns_exhibition e
ON e.id = A.TEventInfoid
WHERE A.TTNum='100229143644'
AND A.TModelid <> 3822
是的,它可以工作,但我仍然认为它太复杂了 – jasonLam
好吧,我用MSSQL – jasonLam