我们的"store"表如下所示:
store_id | city_id | store_type |
---|---|---|
1 | 1 | regular |
2 | 1 | regular |
3 | 1 | regular |
50 | 1 | regular |
51 | 1 | express |
55 | 1 | express |
58 | 1 | express |
70 | 1 | express |
71 | 2 | regular |
75 | 2 | regular |
78 | 2 | regular |
80 | 2 | regular |
85 | 2 | regular |
90 | 2 | regular |
91 | 1 | regular |
95 | 1 | regular |
97 | 1 | regular |
100 | 1 | regular |
105 | 1 | regular |
我想在SQL Server中创建一个在两列上具有相同值的列表,以便在我们的表存储中进行如下所示的 Select :
min_store_id | max_store_id | city_id | store_type |
---|---|---|---|
1 | 50 | 1 | regular |
51 | 70 | 1 | express |
71 | 90 | 2 | regular |
91 | 105 | 1 | regular |
然而,问题是,我们有一个糟糕的store_id系统,因此表中会发生最后一行和第一行的值相同的情况,遗憾的是,我们无法更改它.
试着做了这样的事情:
SELECT MIN(store_id) OVER (PARTITION BY city_id, store_type) AS min_store_id,
MAX(store_id) OVER (PARTITION BY city_id, store_type) AS max_store_id,
city_id,
store_type
FROM store;
但它根本不起作用.