联合查询三张表及子查询实战:

首先我们来建立三张要用到数表：建一个数据库叫UNITDB.

表一:

CREATE TABLE SALLER (

    Saller_id int (20) NOT NULL,

    Saller_name CHAR (60) NOT NULL,

    PRIMARY KEY (Saller_id)

);

表二:

CREATE TABLE products (

    Saller_id int (20),

    Product_id int (20) NOT NULL,

    Product_name CHAR (60) NOT NULL,

    Product_price double (16,2),

    Primary key (saller_id)

);

表三:

CREATE TABLE PRODUCTS_DETAIL (

    Product_id int (20),

    Product_name CHAR (60) NOT NULL,

Product_sum int (20),

Product_date CHAR (60) not null,

PRIMARY KEY (Product_id)

);

输入一些相关的数据：

insert into saller(saller_id,saller_name)values(1003,'sammi');

 

insert into products_detail(product_id,product_name,product_sum,product_date)values(2002,'food',100,20071102)

 

insert into products(saller_id,product_id,product_name,product_price)values(1001,2001,'car',1500)

 

1.查询所有的销售人员的产品总价大于100000块的记录，要显示以下的字段:

Saller_id,Product_id以及销售总价大于100的所有记录.并按升序排序.(提示总价要算出来先)

答:

我们的做法就是首先做将对应的一个产品的总价先查询出来:

SELECT cc.product_price * dd.product_sum as total

FROM products cc,products_detail dd

WHERE  cc.product_id=dd.product_id

接着将结果是大于100000块的选择出来

 

select a.saller_name,b.product_name

from saller a, products b,products_detail c

where

(SELECT max(cc.product_price * dd.product_sum) as total FROM products cc,products_detail dd WHERE  cc.product_id=dd.product_id) >100000

 

2.查询一月份销售人员前10名售出产品总价大于100000块的销售人员的记录.

答:

select a.saller_name,b.product_name

from saller a, products b,products_detail c

where

(SELECT max(cc.product_price * dd.product_sum) as total

 FROM products cc,products_detail dd

 WHERE  cc.product_id=dd.product_id

 ORDER BY total desc limit 10 offset 0)  >100000

and c.product_date between 20070101 and  20070131;