在线等待!QQ:18940066 有数据库高手请指教哦!还有新年红包送噢!
钱虽不多!心意在!祝各位新年快乐!万事如意!视图语句
CREATE VIEW dbo.VIEW_operator_wholesale_count
AS
SELECT a.operator AS operator, a.et_worker AS et_worker,
a.wholesale_export_no AS wholesale_export_no,
(SELECT customer_name
FROM drug_customer_catalog
WHERE customer_id = a.customer_id) AS customer_name, a.wholesale_date,
(SELECT CONVERT(money, isnull(SUM(realsale_price * quantity), 0))
FROM wholesale_export_memos
WHERE wholesale_export_no = a.wholesale_export_no) AS sum_money,
(SELECT CONVERT(money, isnull(SUM((realsale_price - stock_price) * quantity), 0))
FROM wholesale_export_memos
WHERE wholesale_export_no = a.wholesale_export_no) AS sum_gross,
(SELECT COUNT(DISTINCT drug_code)
FROM wholesale_export_memos
WHERE wholesale_export_no = a.wholesale_export_no) AS drug_count
FROM wholesale_export_main a INNER JOIN
wholesale_export_memos b ON
a.wholesale_export_no = b.wholesale_export_no
GROUP BY a.operator, a.et_worker, a.wholesale_export_no, a.wholesale_date,
a.customer_id
UNION
SELECT c.opeater AS operator, c.deal_man AS et_worker,
c.move_export_no AS wholesale_export_no,
(SELECT customer_name
FROM drug_customer_catalog
WHERE customer_id = c.move_units_no) AS customer_name, c.ticket_date,
(SELECT CONVERT(money, isnull(SUM(stock_price * quantity), 0))
FROM move_export_memos
WHERE move_export_no = c.move_export_no) AS sum_money,
(SELECT CONVERT(money, isnull(SUM((stock_price) * quantity), 0))
FROM move_export_memos
WHERE move_export_no = c.move_export_no) AS sum_gross,
(SELECT COUNT(DISTINCT drug_code)
FROM move_export_memos
WHERE move_export_no = c.move_export_no) AS drug_count
FROM move_export_main c INNER JOIN
move_export_memos d ON c.move_export_no = d .move_export_no
GROUP BY c.opeater, c.deal_man, c.move_export_no, c.ticket_date, c.move_units_no
钱虽不多!心意在!祝各位新年快乐!万事如意!视图语句
CREATE VIEW dbo.VIEW_operator_wholesale_count
AS
SELECT a.operator AS operator, a.et_worker AS et_worker,
a.wholesale_export_no AS wholesale_export_no,
(SELECT customer_name
FROM drug_customer_catalog
WHERE customer_id = a.customer_id) AS customer_name, a.wholesale_date,
(SELECT CONVERT(money, isnull(SUM(realsale_price * quantity), 0))
FROM wholesale_export_memos
WHERE wholesale_export_no = a.wholesale_export_no) AS sum_money,
(SELECT CONVERT(money, isnull(SUM((realsale_price - stock_price) * quantity), 0))
FROM wholesale_export_memos
WHERE wholesale_export_no = a.wholesale_export_no) AS sum_gross,
(SELECT COUNT(DISTINCT drug_code)
FROM wholesale_export_memos
WHERE wholesale_export_no = a.wholesale_export_no) AS drug_count
FROM wholesale_export_main a INNER JOIN
wholesale_export_memos b ON
a.wholesale_export_no = b.wholesale_export_no
GROUP BY a.operator, a.et_worker, a.wholesale_export_no, a.wholesale_date,
a.customer_id
UNION
SELECT c.opeater AS operator, c.deal_man AS et_worker,
c.move_export_no AS wholesale_export_no,
(SELECT customer_name
FROM drug_customer_catalog
WHERE customer_id = c.move_units_no) AS customer_name, c.ticket_date,
(SELECT CONVERT(money, isnull(SUM(stock_price * quantity), 0))
FROM move_export_memos
WHERE move_export_no = c.move_export_no) AS sum_money,
(SELECT CONVERT(money, isnull(SUM((stock_price) * quantity), 0))
FROM move_export_memos
WHERE move_export_no = c.move_export_no) AS sum_gross,
(SELECT COUNT(DISTINCT drug_code)
FROM move_export_memos
WHERE move_export_no = c.move_export_no) AS drug_count
FROM move_export_main c INNER JOIN
move_export_memos d ON c.move_export_no = d .move_export_no
GROUP BY c.opeater, c.deal_man, c.move_export_no, c.ticket_date, c.move_units_no
用where+限制条件就可以了
具体限制条件看你需要的功能
对楼主sql语句格式写法很不欣赏,看的眼都花了~~~~~~~~~~
方法2:在客户SELECT查询语句中加相应的WHERE条件