sql-server - SQL用值列表替换null

Question

我有一张City和StatePopulation桌子

CREATE TABLE dbo.City 
(
     Code INT, 
     CityName VARCHAR(30)
)

INSERT INTO CITY (CODE, CityName)
   select 1, 'Woodside'
   union
   select 2, 'Sunnyside'
   union
   select 3, 'Flushing'
   union
   select 4, 'Elmhurst'
   union
   select 5, 'ForestHills'
   union
   select 6, 'Manhattan'
   union
   select 7, 'Atlanta'
   union
   select 8, 'Alpharetta'
   union
   select 9, 'Johns Creek'

CREATE TABLE DBO.CityPopulation 
(
     StateCode VARCHAR(10), 
     CityCode INT, 
     PopulationCount INT
)

INSERT INTO DBO.CityPopulation (StateCode, CityCode, PopulationCount)
   SELECT 'NY', 1, 1000
   UNION
   SELECT 'NY', 2, 1500
   UNION
   SELECT 'NY', 3, 2500
   UNION
   SELECT 'NY', 4, 3000
   UNION
   SELECT 'NY', 5, 3500
   UNION
   SELECT 'NY', 6, 4000
   UNION
   SELECT 'GA', 7, 5500
   UNION
   SELECT 'GA', 8, 1200
   UNION
   SELECT 'GA', 9, 1900

CREATE TYPE dbo.UDTT_StateType AS TABLE (StateCode VARCHAR(10), CityCode INT)

DECLAR @State dbo.UDTT_StateType

INSERT INTO @State (StateCode, CityCode)
   select 'NY', null
   union
   select 'GA', 8

现在我需要返回与 and 匹配的值的行，但是CityPopulation当' NY' 和为空时，我需要返回 CityCode 1 和 2 的数据UDTT_StateTypeStateCodeCityCodeStateCodeCity Code

谁能帮我写这个查询，好吗？

标签： sql-server

Answer 1

我能够通过 CROSS JOIN 解决这个问题。

SELECT cpd.StateCode, cpd.CityCode, cpd.CityName, cpd.PopulationCount 
FROM @State AS s 
CROSS JOIN (SELECT * FROM CityPopulation AS cp INNER JOIN city AS c ON cp.CityCode = c.Code 
                WHERE CityCode IN (1,2)) AS cpd 
WHERE s.CityCode = cpd.CityCode OR s.CityCode IS NULL

这产生了以下结果：