时间戳

首页 > 解决方案 > TSQL to find Null values

sql - TSQL to find Null values

问题描述

I have the following tables:

Product 

PRODUCT_ID  PRODUCT_NAME
1           SCREW1.20
2           SCREW1.23
3           SCREW2.5
4           SCREW3.10`

Sales

SALE_ID  PRODUCT_ID  YEAR   QUANTITY    PRICE
1        1           2019   14          1.64
2        2           2011   20          2.13
3        3           2018   22          2.29
4        4           2019   18          2.02
5        1           2016   13          2.42
6        2           2019   13          2.36
7        3           2013   20          2.38
8        4           2018   20          2.20
9        1           2010   23          1.69
10       2           2016   22          1.84
11       3           2015   12          1.88
12       4           2009   23          2.47
13       1           2019   14          1.57
14       2           2013   16          1.32
15       3           2016   17          1.66
16       4           2016   16          2.11
17       1           2009   13          2.33
18       2           2015   13          1.84
19       3           2016   12          1.73
20       4           2015   19          1.98
21       1           2018   22          2.17
22       2           2014   21          1.63
23       3           2009   19          1.85
24       4           2010   14          2.17
25       1           2014   13          2.09
26       2           2018   13          1.78
27       3           2011   20          1.34
28       4           2020   22          2.28
29       1           2013   16          1.96
30       2           2016   13          1.37
31       3           2010   21          1.53
32       4           2012   22          2.53
33       1           2019   18          2.57
34       2           2015   22          1.69
35       3           2020   12          2.18
36       4           2019   18          2.63
37       1           2011   21          2.05
38       2           2010   18          1.47
39       3           2012   15          2.37
40       4           2016   21          2.15
41       1           2017   20          1.28
42       2           2019   17          2.13
43       3           2017   14          2.27
44       4           2017   13          1.63
45       1           2009   23          1.89
46       2           2010   13          2.49
47       3           2020   12          2.19
48       4           2009   16          2.34
49       1           2018   13          2.37
50       2           2013   23          1.24
51       3           2011   21          1.58
52       4           2020   21          1.96
53       1           2014   14          1.53
54       2           2015   12          1.85
55       3           2014   13          1.23
56       4           2009   17          2.26
57       1           2017   22          2.41
58       2           2017   20          2.19
59       3           2016   13          1.47
60       4           2018   17          2.05
61       1           2020   15          2.22
62       2           2020   18          1.63
63       3           2016   15          2.67
64       4           2012   18          2.40
65       1           2018   17          2.16
66       2           2017   19          1.57
67       3           2014   23          1.82
68       4           2017   22          2.27
69       1           2019   16          2.65
70       2           2011   16          2.37
71       3           2014   20          1.53
72       4           2014   17          1.81
73       1           2009   23          1.40
74       2           2010   16          2.13
75       3           2013   20          2.27
76       4           2016   17          2.09
77       1           2013   19          2.30
78       2           2016   14          1.40
79       3           2012   12          1.75
80       4           2013   15          1.41
81       1           2015   21          2.30
82       2           2019   16          1.81

I want to find for each year the products which do not have any sales at all

So my train of thought is to find the distinct years for the sales, for each of these iterate for every product, for each product determine the sales: sum(quantity * price).

The result would have lines like these

YEAR    PRODUCT_NAME   Sales
(...)
2011    SCREW3.10      NULL
2012    SCREW3.10      98.96 (<- this record should not appear)
(...)

标签： sqlsql-servertsqldatejoin

解决方案

您可以cross join将products带有不同年份的表sales带上，然后将sales表带上left join。当left join空出来的时候，你知道你有一个在那一年没有售出的产品：

select p.*, y.year
from products p
cross join (select distinct year from sales) y
left join sales s on s.year = y.year and s.product_id = p.product_id
where s.sale_id is null

您也可以用not exists代替来表达这一点left join ... where ... is null：

select p.*, y.year
from products p
cross join (select distinct year from sales) y
where not exists (
    select 1 from sales s where s.year = y.year and s.product_id = p.product_id
)

推荐阅读