回答編集履歴
3
推敲
test
CHANGED
|
@@ -46,9 +46,9 @@
|
|
|
46
46
|
|
|
47
47
|
select ms.name
|
|
48
48
|
|
|
49
|
-
, COUNT(
|
|
49
|
+
, COUNT(mc.id) as clients_num
|
|
50
50
|
|
|
51
|
-
, COUNT(
|
|
51
|
+
, COUNT(case when mc.mobileapp = 1 then mc.id end) as app_clients_num
|
|
52
52
|
|
|
53
53
|
from m_shop as ms
|
|
54
54
|
|
2
推敲
test
CHANGED
|
@@ -56,14 +56,14 @@
|
|
|
56
56
|
|
|
57
57
|
on sa.client_id = ms.id
|
|
58
58
|
|
|
59
|
-
and sa.settlement_date::date >= '2021-07-01'
|
|
60
|
-
|
|
61
59
|
left join m_client as mc
|
|
62
60
|
|
|
63
61
|
on mc.shop_id = ms.id
|
|
64
62
|
|
|
65
63
|
where ms.deleted_at is null
|
|
66
64
|
|
|
65
|
+
and sa.settlement_date::date >= '2021-07-01'
|
|
66
|
+
|
|
67
67
|
group by ms.name
|
|
68
68
|
|
|
69
69
|
```
|
1
追記
test
CHANGED
|
@@ -37,3 +37,33 @@
|
|
|
37
37
|
※mc2への結合はmc1との結合と違いがありませんが、誤記ではないですか？
|
|
38
38
|
|
|
39
39
|
※項目名は全て小文字の様ですから、`"`で括るのは大文字を識別させたい時のみですので、必要はありません。
|
|
40
|
+
|
|
41
|
+
|
|
42
|
+
|
|
43
|
+
### 追記
|
|
44
|
+
|
|
45
|
+
```SQL
|
|
46
|
+
|
|
47
|
+
select ms.name
|
|
48
|
+
|
|
49
|
+
, COUNT(DISTINCT mc.id) as clients_num
|
|
50
|
+
|
|
51
|
+
, COUNT(DISTINCT case when mobileapp = 1 then mc.id end) as app_clients_num
|
|
52
|
+
|
|
53
|
+
from m_shop as ms
|
|
54
|
+
|
|
55
|
+
inner join sales as sa
|
|
56
|
+
|
|
57
|
+
on sa.client_id = ms.id
|
|
58
|
+
|
|
59
|
+
and sa.settlement_date::date >= '2021-07-01'
|
|
60
|
+
|
|
61
|
+
left join m_client as mc
|
|
62
|
+
|
|
63
|
+
on mc.shop_id = ms.id
|
|
64
|
+
|
|
65
|
+
where ms.deleted_at is null
|
|
66
|
+
|
|
67
|
+
group by ms.name
|
|
68
|
+
|
|
69
|
+
```
|