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 |
【推荐】还在用 ECharts 开发大屏?试试这款永久免费的开源 BI 工具!
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 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协议为什么如此重要!