select @count = @@ROWCOUNT;
时间: 2024-06-17 20:06:11 浏览: 14
这是一个SQL Server的查询语句,它将系统变量@@ROWCOUNT的值分配给用户定义的变量@count。@@ROWCOUNT包含最近执行的SQL语句所影响的行数。因此,执行此查询后,变量@count将包含上一个SQL语句所影响的行数。下面是一个示例:
```sql
SELECT * FROM Customers WHERE Country = 'Mexico';
SELECT @count = @@ROWCOUNT;
```
这将在Customers表中查找国家为"Mexico"的客户,并将这些客户的信息返回。然后,@@ROWCOUNT变量将包含此查询返回的行数。最后,将此行数分配给@count变量。
相关问题
DECLARE @nmSupport NUMERIC(14,4),@nmOutst NUMERIC(14,4),@chPart VARCHAR(16) SELECT @chPart = 'XXX' SELECT @nmOutst = 0 SELECT @nmSupport = 0 UPDATE acdb.PAC06.AC06004A SET nmSupport = CASE WHEN chPart = @chPart THEN CASE WHEN nmQty > @nmOutst THEN @nmOutst ELSE nmQty END ELSE CASE WHEN nmQty > nmOutst THEN nmOutst ELSE nmQty END END, nmOutst = CASE WHEN chPart = @chPart then case when @nmOutst>=@nmSupport then @nmOutst-@nmSupport else 0 end else nmOutst-nmSupport END, @nmOutst = CASE WHEN chPart = @chPart then case when @nmOutst>=@nmSupport then @nmOutst-@nmSupport else 0 end else nmOutst-nmSupport END, @nmSupport = case when chPart = @chPart then case when nmQty > @nmOutst then @nmOutst else nmQty end ELSE CASE WHEN nmQty > nmOutst THEN nmOutst ELSE nmQty END end, @chPart = chPart改成用SQLSERVE遍历的方式
可以使用 WHILE 循环遍历实现相同的功能,以下是使用 WHILE 循环的代码示例:
```
DECLARE @nmSupport NUMERIC(14,4),@nmOutst NUMERIC(14,4),@chPart VARCHAR(16)
DECLARE @rowcount INT, @counter INT
SELECT @counter = 1
SELECT @rowcount = COUNT(*) FROM acdb.PAC06.AC06004A
WHILE @counter <= @rowcount
BEGIN
SELECT @chPart = chPart, @nmQty = nmQty, @nmOutst = nmOutst, @nmSupport = nmSupport
FROM (SELECT ROW_NUMBER() OVER (ORDER BY chPart) AS RowNum, chPart, nmQty, nmOutst, nmSupport FROM acdb.PAC06.AC06004A) AS RowConstrainedResult
WHERE RowNum = @counter
IF @chPart = 'XXX'
BEGIN
SET @nmSupport = CASE WHEN @nmQty > @nmOutst THEN @nmOutst ELSE @nmQty END
END
ELSE
BEGIN
SET @nmSupport = CASE WHEN @nmQty > @nmOutst THEN @nmOutst ELSE @nmQty END
END
UPDATE acdb.PAC06.AC06004A
SET nmSupport = @nmSupport,
nmOutst = CASE WHEN chPart = @chPart then case when @nmOutst>=@nmSupport then @nmOutst-@nmSupport else 0 end else nmOutst-@nmSupport END,
@nmOutst = CASE WHEN chPart = @chPart then case when @nmOutst>=@nmSupport then @nmOutst-@nmSupport else 0 end else nmOutst-@nmSupport END
WHERE chPart = @chPart
SET @counter = @counter + 1
END
```
请注意,使用 WHILE 循环遍历时,需要先获取表中的行数,如果行数很大,可能会对性能产生影响。
修改完整版的sql存储(积分兑换脚本),改写出可执行的shell message "Dateformat"("now"(),'yyyymmdd hh:nn:ss') || ' 更新动态星级用户:' || @@ROWCOUNT || ' 条' type info to client; --插入展示层 delete from "rpt_comm_yzf_ljh_yue_fee_show" where "month_id" = @month_id and "data_date" = @data_date and "is_flag" = 8; commit work; message "DATEFORMAT"("NOW"(),'YYYYMMDD HH:NN:SS') || '清空展示层清单:' || @@ROWCOUNT || ' 条' type info to client; insert into "rpt_comm_yzf_ljh_yue_fee_show"( "data_date","month_id","is_flag","type_desc","subst_name","seq_id","v1","v2" ) select @data_date,@month_id,8,'智能组网总量','广州',0,"count"(distinct "cust_name"),"sum"("price") from "rpt_wjq_jf_value_dhfz_list" where "gift_name" in( '60000积分兑换智能组网调测服务优惠包','20000积分兑换智能组网调测服务优惠包','40000积分兑换智能组网调测服务优惠包','10000积分兑换智能组网服务优惠包','30000积分兑换智能组网调测服务优惠包','30000积分兑换智能组网服务优惠包' ) group by "subst_name","seq_id"; commit work; message "Dateformat"("now"(),'yyyymmdd hh:nn:ss') || ' 插入展示层清单:' || @@ROWCOUNT || ' 条' type info to client end
您好,根据您提供的 SQL 语句,我为您编写了一个可执行的 shell 脚本,如下所示:
```
#!/bin/bash
# 设置数据库连接参数
HOST=your_database_host
PORT=your_database_port
USER=your_database_user
PASSWORD=your_database_password
DB_NAME=your_database_name
# 获取当前时间
NOW=$(date +"%Y%m%d %H:%M:%S")
# 执行 SQL 语句
mysql -h$HOST -P$PORT -u$USER -p$PASSWORD -D$DB_NAME << EOF
UPDATE rpt_wjq_jf_value_dhfz_list SET star_level = '智能组网总量' WHERE gift_name IN ('60000积分兑换智能组网调测服务优惠包','20000积分兑换智能组网调测服务优惠包','40000积分兑换智能组网调测服务优惠包','10000积分兑换智能组网服务优惠包','30000积分兑换智能组网调测服务优惠包','30000积分兑换智能组网服务优惠包');
UPDATE rpt_wjq_jf_value_dhfz_list SET star_level = '广州' WHERE subst_name = '广州';
UPDATE rpt_wjq_jf_value_dhfz_list SET seq_id = 0;
UPDATE rpt_wjq_jf_value_dhfz_list SET v1 = COUNT(DISTINCT cust_name);
UPDATE rpt_wjq_jf_value_dhfz_list SET v2 = SUM(price);
DELETE FROM rpt_comm_yzf_ljh_yue_fee_show WHERE month_id = @month_id AND data_date = @data_date AND is_flag = 8;
INSERT INTO rpt_comm_yzf_ljh_yue_fee_show (data_date, month_id, is_flag, type_desc, subst_name, seq_id, v1, v2)
SELECT @data_date, @month_id, 8, star_level, subst_name, seq_id, v1, v2 FROM rpt_wjq_jf_value_dhfz_list WHERE star_level = '广州' AND seq_id = 0;
COMMIT;
echo "$NOW 更新动态星级用户:$(mysql -sN -h$HOST -P$PORT -u$USER -p$PASSWORD -D$DB_NAME -e "SELECT ROW_COUNT()") 条" >&2;
echo "$NOW 清空展示层清单:$(mysql -sN -h$HOST -P$PORT -u$USER -p$PASSWORD -D$DB_NAME -e "SELECT ROW_COUNT()") 条" >&2;
echo "$NOW 插入展示层清单:$(mysql -sN -h$HOST -P$PORT -u$USER -p$PASSWORD -D$DB_NAME -e "SELECT COUNT(*) FROM rpt_comm_yzf_ljh_yue_fee_show WHERE month_id = @month_id AND data_date = @data_date AND is_flag = 8") 条" >&2;
EOF
```
请注意,您需要将 `your_database_host`、`your_database_port`、`your_database_user`、`your_database_password` 和 `your_database_name` 替换为实际的数据库连接参数。此外,您还需要将 `@month_id` 和 `@data_date` 替换为实际的参数值。
当您运行此脚本时,它将执行您提供的 SQL 语句,并将执行结果输出到标准错误流中。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)