在mysql的sakila数据库的payment表中,用sql语句查询出如下格式的数据 显示格式是(一直到12月份): 年份 1月销售额 2月销售额 3月销售额 4月销售额 5月销售额 2005 0 0 XX XX XX XX XX XX XX 2006 0 514.18 XX XX XX XX XX XX XX 表结构为: CREATE TABLE payment ( payment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, customer_id SMALLINT UNSIGNED NOT NULL, staff_id TINYINT UNSIGNED NOT NULL, rental_id INT DEFAULT NULL, amount DECIMAL(5,2) NOT NULL, payment_date DATETIME NOT NULL, last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (payment_id), KEY idx_fk_staff_id (staff_id), KEY idx_fk_customer_id (customer_id), CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8;这个是mysql官方的一个建表语句。
SELECT year(payment_date),CASE month(payment_date) WHEN 01 THEN sum(amount) else 0 END '一月份销售额', CASE month(payment_date) WHEN 02 THEN sum(amount) else 0 END '二月份销售额', CASE month(payment_date) WHEN 03 THEN sum(amount) else 0 END '三月份销售额', CASE month(payment_date) WHEN 04 THEN sum(amount) else 0 END '四月份销售额', CASE month(payment_date) WHEN 05 THEN sum(amount) else 0 END '五月份销售额', CASE month(payment_date) WHEN 06 THEN sum(amount) else 0 END '六月份销售额', CASE month(payment_date) WHEN 07 THEN sum(amount) else 0 END '七月份销售额', CASE month(payment_date) WHEN 08 THEN sum(amount) else 0 END '八月份销售额', CASE month(payment_date) WHEN 09 THEN sum(amount) else 0 END '九月份销售额', CASE month(payment_date) WHEN 10 THEN sum(amount) else 0 END '十月份销售额', CASE month(payment_date) WHEN 11 THEN sum(amount) else 0 END '十一月份销售额', CASE month(payment_date) WHEN 12 THEN sum(amount) else 0 END '十二月份销售额' from payment group by year(payment_date);不知道是不是对的!
CASE month(payment_date) WHEN 02 THEN sum(amount) else 0 END '二月份销售额',-> sum(CASE month(payment_date) WHEN 02 THEN amount else 0 END) as '二月份销售额', or sum(if(month(payment_date)=2,amount,0)) as '二月份销售额',其它的自行修改
SELECT year(payment_date), sum(CASE month(payment_date) WHEN 01 THEN amount END) '一月份销售额', sum(CASE month(payment_date) WHEN 02 THEN amount END) '二月份销售额', sum(CASE month(payment_date) WHEN 03 THEN amount END) '三月份销售额', sum(CASE month(payment_date) WHEN 04 THEN amount END) '四月份销售额', sum(CASE month(payment_date) WHEN 05 THEN amount END) '五月份销售额', sum(CASE month(payment_date) WHEN 06 THEN amount END) '六月份销售额', sum(CASE month(payment_date) WHEN 07 THEN amount END) '七月份销售额', sum(CASE month(payment_date) WHEN 08 THEN amount END) '八月份销售额', sum(CASE month(payment_date) WHEN 09 THEN amount END) '九月份销售额', sum(CASE month(payment_date) WHEN 10 THEN amount END) '十月份销售额', sum(CASE month(payment_date) WHEN 11 THEN amount END) '十一月份销售额', sum(CASE month(payment_date) WHEN 12 THEN amount END) '十二月份销售额' from payment group by year(payment_date);
显示格式是(一直到12月份):
年份 1月销售额 2月销售额 3月销售额 4月销售额 5月销售额
2005 0 0 XX XX XX XX XX XX XX
2006 0 514.18 XX XX XX XX XX XX XX 表结构为:
CREATE TABLE payment (
payment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id SMALLINT UNSIGNED NOT NULL,
staff_id TINYINT UNSIGNED NOT NULL,
rental_id INT DEFAULT NULL,
amount DECIMAL(5,2) NOT NULL,
payment_date DATETIME NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (payment_id),
KEY idx_fk_staff_id (staff_id),
KEY idx_fk_customer_id (customer_id),
CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;这个是mysql官方的一个建表语句。
CASE month(payment_date) WHEN 02 THEN sum(amount) else 0 END '二月份销售额',
CASE month(payment_date) WHEN 03 THEN sum(amount) else 0 END '三月份销售额',
CASE month(payment_date) WHEN 04 THEN sum(amount) else 0 END '四月份销售额',
CASE month(payment_date) WHEN 05 THEN sum(amount) else 0 END '五月份销售额',
CASE month(payment_date) WHEN 06 THEN sum(amount) else 0 END '六月份销售额',
CASE month(payment_date) WHEN 07 THEN sum(amount) else 0 END '七月份销售额',
CASE month(payment_date) WHEN 08 THEN sum(amount) else 0 END '八月份销售额',
CASE month(payment_date) WHEN 09 THEN sum(amount) else 0 END '九月份销售额',
CASE month(payment_date) WHEN 10 THEN sum(amount) else 0 END '十月份销售额',
CASE month(payment_date) WHEN 11 THEN sum(amount) else 0 END '十一月份销售额',
CASE month(payment_date) WHEN 12 THEN sum(amount) else 0 END '十二月份销售额'
from payment group by year(payment_date);不知道是不是对的!
sum(CASE month(payment_date) WHEN 02 THEN amount else 0 END) as '二月份销售额',
or
sum(if(month(payment_date)=2,amount,0)) as '二月份销售额',其它的自行修改
MySQL交叉表
sum(CASE month(payment_date) WHEN 01 THEN amount END) '一月份销售额',
sum(CASE month(payment_date) WHEN 02 THEN amount END) '二月份销售额',
sum(CASE month(payment_date) WHEN 03 THEN amount END) '三月份销售额',
sum(CASE month(payment_date) WHEN 04 THEN amount END) '四月份销售额',
sum(CASE month(payment_date) WHEN 05 THEN amount END) '五月份销售额',
sum(CASE month(payment_date) WHEN 06 THEN amount END) '六月份销售额',
sum(CASE month(payment_date) WHEN 07 THEN amount END) '七月份销售额',
sum(CASE month(payment_date) WHEN 08 THEN amount END) '八月份销售额',
sum(CASE month(payment_date) WHEN 09 THEN amount END) '九月份销售额',
sum(CASE month(payment_date) WHEN 10 THEN amount END) '十月份销售额',
sum(CASE month(payment_date) WHEN 11 THEN amount END) '十一月份销售额',
sum(CASE month(payment_date) WHEN 12 THEN amount END) '十二月份销售额'
from payment
group by year(payment_date);