DROP TABLE IF EXISTS `sakila`.`payment`;
CREATE TABLE `sakila`.`payment` (
`payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`customer_id` smallint(5) unsigned NOT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`rental_id` int(11) DEFAULT NULL,
`amount` decimal(5,2) NOT NULL,
`payment_date` datetime NOT NULL,
`last_update` timestamp NOT NULL 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`),
KEY `fk_payment_rental` (`rental_id`),
CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8;
下面是写的一条查询语句 查询的时候实在是太慢了 有其他的方法么 请教各位大牛:
select *,(select sum(amount) from payment where payment_date<=a.payment_date) as sum_amount from payment a order by payment_date asc;
*,
(SELECT SUM(amount) FROM sakila.payment WHERE payment_id<=a.payment_id ) as 匯總
FROM sakila.payment AS a--如果有其它條件時這樣改,如一個customer_id的行匯總SELECT
*,
(SELECT SUM(amount) FROM sakila.payment WHERE customer_id=a.customer_id AND payment_id<=a.payment_id ) as 匯總
FROM sakila.payment AS a
1 1
2 3
3 6是mysql。
自己測測就知了
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T(ID INT IDENTITY ,[amout] int)
Insert #T
select 1 union all
select 2 union all
select 3
Go
Select * from #T
/*
ID amout
1 1
2 2
3 3
*/
Select *,(SELECT SUM([amout]) FROM #T WHERE ID<=a.ID) AS sum_sumount from #T AS a/*
ID amout sum_sumount
1 1 1
2 2 3
3 3 6
*/
没有按id排序 id是乱的 要求按时间来排序 而且3楼我写的和你的差不多 如果数据比较多的话 执行时间特长 有其他比较好点的方法么