请 sql 老司机

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

For Existing Member Sign In

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

This topic created in 3073 days ago, the information mentioned may be changed or developed.

怎么写 sql 将数字转成占比？

20171101 A 3

20171101 B 5

20171102 A 7

20171102 B 3

=》

20171101 A 3/3+5

20171101 B 5/3+5

20171102 A 7/3+7

20171102 B 3/3+7

SQL

占比

司机

数字

10 replies • 2017-12-08 12:06:06 +08:00

yulitian888

Dec 8, 2017

第一反应是开窗函数，然后看了下是 MySQL，呃，不清楚有没有这个功能，我说的是 Server server

master13

Dec 8, 2017

这种问题自行谷歌或者请新司机解答，老司机不开你这条路

whx20202

Dec 8, 2017

select col1, col2, 字符串拼接函数（ col3，const1，const2 ） from tbl
就可以吧

如果要求数字运算可以搜一下

slgz

Dec 8, 2017

我都没看懂题

xi2008wang

Dec 8, 2017

@master13 提问之前我已经 google 了

mooncakejs

Dec 8, 2017

小学奥数找规律？每日 /B 占比？
不开窗不好搞。

xi2008wang

Dec 8, 2017

咨询部门同事，得到了一个效率不太高的 sql，
select a.date,a.value1,a.value1/b.sum_v from x a,(select date,sum(value1) as sum_v from x ) b where a.date=b.date;

mooncakejs

Dec 8, 2017

支持开窗：
select *,num/sum(num) over d as pct from test window d as (partition by date)
不支持：
select t.*,t.num/g.sum as pct from test t join (select date,sum(num) from test group by date) g on t.date = g.date

xi2008wang

Dec 8, 2017

@xi2008wang 刚刚漏了 group by

select a.date,a.value1,a.value1/b.sum_v from x a,(select date,sum(value1) as sum_v from x group by date) b where a.date=b.date;

xi2008wang

Dec 8, 2017

@mooncakejs 先谢了，我试试