tpcc测试语句_原创精品文档.pdfVIP

tpcc测试语句

TPC-C测试是一个常用的数据库性能测试基准，用于评估数据库系

统的事务处理能力。下面是符合要求的TPC-C测试语句示例：

1.查询订单数量最多的十个客户的姓名和订单数量：

```

SELECTc_name,COUNT(o_id)ASorder_count

FROMcustomer

JOINordersONc_id=o_c_id

GROUPBYc_name

ORDERBYorder_countDESC

LIMIT10;

```

2.查询最近一周内每个仓库的销售总额：

```

SELECTw_id,SUM(ol_amount)AStotal_sales

FROMwarehouse

JOINdistrictONw_id=d_w_id

JOINcustomerONd_w_id=c_w_idANDd_id=c_d_id

JOINordersONc_id=o_c_id

JOINorder_lineONo_id=ol_o_id

WHEREo_date=DATE_SUB(CURDATE(),INTERVAL7DAY)

GROUPBYw_id;

```

3.查询所有未发货的订单的订单号和订单总金额：

```

SELECTo_id,SUM(ol_amount)AStotal_amount

FROMorders

JOINorder_lineONo_id=ol_o_id

WHEREo_carrier_idISNULL

GROUPBYo_id;

```

4.查询每个区域的平均订单数量和平均订单金额：

```

SELECTr_name,AVG(order_count)ASavg_order_count,

AVG(total_amount)ASavg_order_amount

FROM(

SELECTr_name,o_id,COUNT(o_id)ASorder_count,

SUM(ol_amount)AStotal_amount

FROMregion

JOINwarehouseONr_id=w_r_id

JOINdistrictONw_id=d_w_id

JOINordersONd_w_id=o_w_idANDd_id=o_d_id

JOINorder_lineONo_id=ol_o_id

GROUPBYr_name,o_id

)ASsubquery

GROUPBYr_name;

```

5.查询订单数量最多的十个商品的编号和销售数量：

```

SELECTi_id,COUNT(ol_id)ASsales_count

FROMitem

JOINstockONi_id=s_i_id

JOINorder_lineONs_i_id=ol_i_id

GROUPBYi_id

ORDERBYsales_countDESC

LIMIT10;

```

6.查询最近一周内每个雇员处理的订单数量：

```

SELECTe_id,COUNT(o_id)ASorder_count

FROMemployee

JOINordersONe_id=o_e_id

WHEREo_date=DATE_SUB(CURDATE(),INTERVAL7DAY)

GROUPBYe_id;

```

7.查询每个仓库的平均库存数量和平均库存价值：

```

SELECTw_id,AVG(s_quantity)ASavg_stock_quantity,

AVG(s_quantity*i_price)ASavg_stock_value

FROMwarehouse

JOINstockONw_id=s_w_id

JOINitemONs_i_id=i_id

GROUPBYw_id;

```

8.查询每个区域的平均顾客数量和平均顾客订单数量：

```

SELECTr_name,AVG(custome