hao123 百度品专 按品类 计算 下单数量 商品数量 下单金额？

SELECT * FROM t_tag_source WHERE   s_name='hao123';

+--------+----------+---------+--------+---------+---------+------------+------------+------------+------------+----------+
| s_name | s_fname | s_level | tag_id | tag_fid | author | updatetime | level_1_id | level_2_id | level_3_id | category |
+--------+----------+---------+--------+---------+---------+------------+------------+------------+------------+----------+
| hao123 | 外部合作 | 1 | 63 | -2 | kevinlu | 1346155861 | 63 | 0 | 0 | 2 |
+--------+----------+---------+--------+---------+---------+------------+------------+------------+------------+----------+

mysql> SELECT * FROM t_tag_source where s_name like 'SEM%';
+-------------+----------+---------+--------+---------+-----------+------------+------------+------------+------------+----------+
| s_name | s_fname | s_level | tag_id | tag_fid | author | updatetime | level_1_id | level_2_id | level_3_id | category |
+-------------+----------+---------+--------+---------+-----------+------------+------------+------------+------------+----------+
| SEM百度华南 | 外部合作 | 1 | 62 | -2 | winkyye | 1345600539 | 62 | 0 | 0 | 2 |
| SEM百度品专 | 外部合作 | 1 | 69 | -2 | winkyye | 1346833461 | 69 | 0 | 0 | 2 |
| SEM百度华东 | 外部合作 | 1 | 70 | -2 | winkyye | 1346909566 | 70 | 0 | 0 | 2 |
| SEM百度华北 | 外部合作 | 1 | 79 | -2 | winkyye | 1347869030 | 79 | 0 | 0 | 2 |
| SEM搜狗品专 | 外部合作 | 1 | 81 | -2 | winkyye | 1348656505 | 81 | 0 | 0 | 2 |
| SEM奇虎品专 | 外部合作 | 1 | 84 | -2 | winkyye | 1350356677 | 84 | 0 | 0 | 2 |
| SEM360搜索 | 外部合作 | 1 | 85 | -2 | winkyye | 1351222732 | 85 | 0 | 0 | 2 |
| SEM百度华中 | 外部合作 | 1 | 109 | -2 | stevietao | 1354687847 | 109 | 0 | 0 | 2 |
| SEM百度 | 外部合作 | 1 | 128 | -2 | stevietao | 1363922244 | 128 | 0 | 0 | 2 |
| SEM360品专 | 外部合作 | 1 | 252 | -2 | wheatycai | 1398070719 | 252 | 0 | 0 | 2 |
| SEM百度华东 | 百度SEM | 2 | 12801 | 128 | stevietao | 1363922291 | 128 | 1 | 0 | 2 |
| SEM百度华南 | 百度SEM | 2 | 12802 | 128 | stevietao | 1363922300 | 128 | 2 | 0 | 2 |
| SEM百度华北 | 百度SEM | 2 | 12803 | 128 | stevietao | 1363922330 | 128 | 3 | 0 | 2 |
| SEM百度华中 | 百度SEM | 2 | 12804 | 128 | stevietao | 1363922344 | 128 | 4 | 0 | 2 |
| SEM百度深圳 | SEM百度 | 2 | 12805 | 128 | stevietao | 1367116894 | 128 | 5 | 0 | 2 |
| SEM百度网盟 | SEM百度 | 2 | 12806 | 128 | ken | 1371611403 | 128 | 6 | 0 | 2 |
| SEM易迅活动 | SEM百度 | 2 | 12807 | 128 | ken | 1372989539 | 128 | 7 | 0 | 2 |
| SEM百度测试 | SEM百度 | 2 | 12811 | 128 | 闄跺洯 | 1389871496 | 128 | 11 | 0 | 2 |
| SEM | 可乐项目 | 2 | 24812 | 248 | 钄＄喒 | 1392704926 | 248 | 12 | 0 | 2 |
+-------------+----------+---------+--------+---------+-----------+------------+------------+------------+------------+----------+
19 rows in set (0.01 sec)

从结果看出，hao123 tag_id 63  SEM百度品专 tag_id 69   他们都是level_1 级别的

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
--百度品专
select COUNT(distinct OId) as 下单数量,
        c1name as 大类,
        SUM(Qty) as 商品数量,
        SUM(OFee) as  下单金额
        from(
select SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag)) as o ,* from (
SELECT ISNULL(tr.ext_11002, '') as OTag,
       td.Fbusiness_deal_id as OId,
       CONVERT(VARCHAR, td.Fdeal_gen_time, 112) OrderDate,
       tr.Ftrade_buy_num as Qty,
       (tr.Ftrade_buy_num*tr.Ftrade_buy_price / 100.00) as OFee,
       ISNULL(u.Ficson_uid,0) as UId,
       (case when td.Fdeal_source in(2,3,8) then 1969 else td.fsite_id end) as wh_id,
       prod.ProductName, c1.c1name
from t_deal td
left join t_trade tr on td.Fdeal_id = tr.fdeal_id
left join etl_users..t_user_buyer u on u.Fwg_uid=td.Fbuyer_id
LEFT JOIN etl0_icson_product..product prod ON tr.Fitem_local_product_id = prod.sysno
LEFT JOIN etl0_icson_product..category1 c1 ON c1.sysno = prod.c1sysno
--WHERE tr.fdeal_id = 1404002103
--where ISNULL(tr.ext_11002, '') LIKE '22102000173%'
where td.Fdeal_gen_time >= '2014-03-01 00:00:00.000' AND td.Fdeal_gen_time < '2014-03-02 00:00:00.000' and td.Fdeal_property3 = 64 and td.Fdeal_source not in(2,3,8))t
where --SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag))='690100016300000'
 SUBSTRING (t.Otag, 1, 2)='69' )y
group by c1name
 
--hao123
select COUNT(distinct OId) as 下单数量,
        c1name as 大类,
        SUM(Qty) as 商品数量,
        SUM(OFee) as  下单金额
        from(
select SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag)) as o ,* from (
SELECT ISNULL(tr.ext_11002, '') as OTag,
       td.Fbusiness_deal_id as OId,
       CONVERT(VARCHAR, td.Fdeal_gen_time, 112) OrderDate,
       tr.Ftrade_buy_num as Qty,
       (tr.Ftrade_buy_num*tr.Ftrade_buy_price / 100.00) as OFee,
       ISNULL(u.Ficson_uid,0) as UId,
       (case when td.Fdeal_source in(2,3,8) then 1969 else td.fsite_id end) as wh_id,
       prod.ProductName, c1.c1name
from t_deal td
left join t_trade tr on td.Fdeal_id = tr.fdeal_id
left join etl_users..t_user_buyer u on u.Fwg_uid=td.Fbuyer_id
LEFT JOIN etl0_icson_product..product prod ON tr.Fitem_local_product_id = prod.sysno
LEFT JOIN etl0_icson_product..category1 c1 ON c1.sysno = prod.c1sysno
--WHERE tr.fdeal_id = 1404002103
--where ISNULL(tr.ext_11002, '') LIKE '22102000173%'
where td.Fdeal_gen_time >= '2014-09-03 00:00:00.000' AND td.Fdeal_gen_time < '2014-09-04 00:00:00.000' and td.Fdeal_property3 = 64 and td.Fdeal_source not in(2,3,8))t
where --SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag))='690100016300000'
 SUBSTRING (t.Otag, 1, 2)='63' )y
group by c1name

2014年6月份之前的数据计算方式

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
--百度品专
select COUNT(distinct OId) as 下单数量,
        c1name as 大类,
        SUM(Qty) as 商品数量,
        SUM(OFee) as  下单金额
        from(
select SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag)) as o ,* from (
SELECT d.otag as OTag,
       m.soid as OId,
       CONVERT(VARCHAR, m.orderdate, 112) OrderDate,
       d.quantity as Qty,
       (d.price * d.quantity) as OFee,
       m.customersysno as UId,
       m.siteno as wh_id,
       prod.ProductName, c1.c1name
from etl0_icson_cs..so_master m INNER JOIN
etl0_icson_cs..so_item d ON m.sysno = d.sosysno
LEFT JOIN etl0_icson_product..product prod ON d.productsysno = prod.sysno
LEFT JOIN etl0_icson_product..category1 c1 ON c1.sysno = prod.c1sysno
where m.orderdate >= '2014-03-01' AND m.orderdate < '2014-04-01') t
where --SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag))='690100016300000'
 SUBSTRING (t.Otag, 1, 2)='69' )y
group by c1name
 
--hao123
select COUNT(distinct OId) as 下单数量,
        c1name as 大类,
        SUM(Qty) as 商品数量,
        SUM(OFee) as  下单金额
        from(
select SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag)) as o ,* from (
SELECT d.otag as OTag,
       m.soid as OId,
       CONVERT(VARCHAR, m.orderdate, 112) OrderDate,
       d.quantity as Qty,
       (d.price * d.quantity) as OFee,
       m.customersysno as UId,
       m.siteno as wh_id,
       prod.ProductName, c1.c1name
from etl0_icson_cs..so_master m INNER JOIN
etl0_icson_cs..so_item d ON m.sysno = d.sosysno
LEFT JOIN etl0_icson_product..product prod ON d.productsysno = prod.sysno
LEFT JOIN etl0_icson_product..category1 c1 ON c1.sysno = prod.c1sysno
where m.orderdate >= '2014-03-01' AND m.orderdate < '2014-04-01') t
where --SUBSTRING (t.Otag, 0, CHARINDEX('-',OTag))='690100016300000'
 SUBSTRING (t.Otag, 1, 2)='63' )y
group by c1name

　　

 

 

posted on   阮佳佳  阅读(446)  评论(0)  编辑  收藏  举报

编辑推荐：
· ASP.NET Core 模型验证消息的本地化新姿势
· 对象命名为何需要避免'-er'和'-or'后缀
· SQL Server如何跟踪自动统计信息更新?
· AI与.NET技术实操系列：使用Catalyst进行自然语言处理
· 分享一个我遇到过的“量子力学”级别的BUG。
阅读排行：
· C# 中比较实用的关键字，基础高频面试题！
· .NET 10 Preview 2 增强了 Blazor 和.NET MAUI
· 为什么AI教师难以实现
· 如何让低于1B参数的小型语言模型实现 100% 的准确率
· AI Agent爆火后，MCP协议为什么如此重要！

导航

统计

点击右上角即可分享
微信分享提示