回答編集履歴
3
推敲
answer
CHANGED
|
@@ -22,8 +22,8 @@
|
|
|
22
22
|
### 追記
|
|
23
23
|
```SQL
|
|
24
24
|
select ms.name
|
|
25
|
-
, COUNT(
|
|
25
|
+
, COUNT(mc.id) as clients_num
|
|
26
|
-
, COUNT(
|
|
26
|
+
, COUNT(case when mc.mobileapp = 1 then mc.id end) as app_clients_num
|
|
27
27
|
from m_shop as ms
|
|
28
28
|
inner join sales as sa
|
|
29
29
|
on sa.client_id = ms.id
|
2
推敲
answer
CHANGED
|
@@ -27,9 +27,9 @@
|
|
|
27
27
|
from m_shop as ms
|
|
28
28
|
inner join sales as sa
|
|
29
29
|
on sa.client_id = ms.id
|
|
30
|
-
and sa.settlement_date::date >= '2021-07-01'
|
|
31
30
|
left join m_client as mc
|
|
32
31
|
on mc.shop_id = ms.id
|
|
33
32
|
where ms.deleted_at is null
|
|
33
|
+
and sa.settlement_date::date >= '2021-07-01'
|
|
34
34
|
group by ms.name
|
|
35
35
|
```
|
1
追記
answer
CHANGED
|
@@ -17,4 +17,19 @@
|
|
|
17
17
|
group by ms.name
|
|
18
18
|
```
|
|
19
19
|
※mc2への結合はmc1との結合と違いがありませんが、誤記ではないですか？
|
|
20
|
-
※項目名は全て小文字の様ですから、`"`で括るのは大文字を識別させたい時のみですので、必要はありません。
|
|
20
|
+
※項目名は全て小文字の様ですから、`"`で括るのは大文字を識別させたい時のみですので、必要はありません。
|
|
21
|
+
|
|
22
|
+
### 追記
|
|
23
|
+
```SQL
|
|
24
|
+
select ms.name
|
|
25
|
+
, COUNT(DISTINCT mc.id) as clients_num
|
|
26
|
+
, COUNT(DISTINCT case when mobileapp = 1 then mc.id end) as app_clients_num
|
|
27
|
+
from m_shop as ms
|
|
28
|
+
inner join sales as sa
|
|
29
|
+
on sa.client_id = ms.id
|
|
30
|
+
and sa.settlement_date::date >= '2021-07-01'
|
|
31
|
+
left join m_client as mc
|
|
32
|
+
on mc.shop_id = ms.id
|
|
33
|
+
where ms.deleted_at is null
|
|
34
|
+
group by ms.name
|
|
35
|
+
```
|