很抱歉,如果以前问过,我梳理了很多问题,但是找不到答案,我可以应用于此问题。
我正在整理我只读访问数据库的SSRS报告,并且很难以我想要的格式选择数据。
Table 1: Referenced Data
==== =============
ID Description
==== =============
1 Apple
2 Orange
3 Pear
==== =============
Table 2: Records
==== ====== ========
ID Data Tag
==== ====== ========
1 1 List
2 2 List
3 Red String
4 Blue String
5 3 List
==== ====== ========
我想看到的是:
Report
========== =========
RecordNo Content
========== =========
1 Apple
2 Orange
3 Red
4 Blue
5 Pear
========== =========
问题是T2的数据列是NVARCHAR,而T1的ID列为Bigint。虽然我可以通过使用列表标签选择行来避免转换错误,但我丢失了字符串数据。
有什么办法可以在选择语句中做我想做的事情?感觉这是可以通过一些创造力来完成的,但是我仍在学习过程中,而我的SQL还没有。
SELECT
rec.Id
,Content = CASE WHEN data.Id IS NOT NULL THEN data.Description ELSE rec.Data END
FROM
Records rec
LEFT JOIN ReferencedData data
ON rec.Tag <> 'String'
AND rec.Data = CAST(data.Id AS NVARCHAR(50))
您可以将BIGINT
施放为NVARCHAR
作为联接条件的一部分,这将避免隐式转换错误。
您可以尝试这样的事情:
;WITH DataNum AS
(
SELECT ID, Data FROM Records WHERE ISNUMERIC(Data) = 1
)
SELECT dn.ID, rd.Description
FROM DataNum dn
JOIN ReferencedData rd ON dn.Data = rd.ID
UNION ALL
SELECT ID, Data
FROM Records
WHERE ISNUMERIC(Data) <> 1
select table2.id
, coalesce(table1.description, table2.Data) content
from table2
left join table1
on table2.tag <> 'String'
and table1.id = table2.data
order by table2.id
这在MySQL(SQL小提琴)中起作用,但需要稍微更改SQL Server(SQL Fiddle)和Oracle(SQL Fiddle):
select table2.id
, coalesce(table1.description,table2.Data) content
from table2
left join table1
on table2.tag <> 'String'
and table1.id = case when table2.tag <> 'String' then table2.data end
order by table2.id
和postgresql(SQL小提琴)需要再一个tweek:
select table2.id
, coalesce(table1.description,table2.Data) as content
from table2
left join table1
on table2.tag <> 'String'
and cast(table1.id as varchar(6)) = table2.data
order by table2.id