如何动态地做联合运算符并获取记录 [英] How to dynamically do union operator and get records

问题描述

我有大约24个表（JanSales13 - DecSales14），每个月的列完全相同。我希望能够动态地或者可选地加入每个表。这是动态选择表格（例如，FebSales13& JulySales13或DecSales13& AprilSales14）。首先，我需要将所选/选定的表联合起来。在union运算符组合这些表之后，我想选择一些列，然后聚合它们的事务数据。逻辑：能够选择任何两个表。然后表格应合并为一个。在UNION操作员之后，获取工会表的记录



这就是我到目前为止所拥有的。动态联合表。用户应该能够选择任何表：

Hi,
I have about 24 tables (JanSales13 – DecSales14) with exact the same columns for each month. I want to be able to join each of these tables dynamically or alternatively. That is to dynamically choose the tables (eg.FebSales13 & JulySales13 OR DecSales13 & AprilSales14 ). First of all I need to union the chosen /selected tables. After the union operator combines these tables, I want to select some columns and then aggregate their transactional data. The logic: To be able to choose any two tables. The tables should then combine into one. After the UNION operator, get record of the union table

That’s what I have so far. A dynamic union table. The user should be able to choose any table:

CREATE PROC spCombine
   @Table_Name  sysname,
   @Table_Name2  sysname
AS
BEGIN
  SET NOCOUNT OFF;

DECLARE @Dynamictbl nvarchar(MAX)

SET @Dynamictbl = 
N'SELECT * FROM ' + @Table_Name +
' UNION
SELECT * FROM ' + @Table_Name2

EXECUTE sp_executesql @Dynamictbl

END

但是我我想在这个商店程序中添加一个查询来获取两个组合表的记录

But I want to also add a query within this store procedure to get records of the two combined tables

SELECT
 Product, Description,
 Sum(A_Sales ) AS [A_salesFeb],
 Sum(A_Sales ) AS [A_salesMay],
 Sum(A_Sales ) AS [B_salesFeb],
 Sum(A_Sales ) AS [B_salesMay],
 Sum(A_Sales ) AS [C_salesFeb],
 Sum(A_Sales ) AS [C_salesMay],
FROM ……
GROUP BY Product, Description

所以我的期望是如果我执行商店程序，则会发生以下三个步骤

So my expectation is if I execute the store procedure, the below three steps take place

EXEC spCombine @Table_Name =’ FebSales13’, @Table_Name2=’ MaySales13’

1.Choose any two tables
2.Combine them together
3.Results from main query

图像说明： [ ^ ]

推荐答案

多么糟糕的数据库设计！你必须重新设计它！



而不是将数据存储在几个表中（名称来自月份和年份），创建单个表，例如：销售。

每条记录都应标有时间戳，然后你就能从单张表中获取数据！



试试！

What a bad database design! You have to re-design it!

Instead of storing data in few tables (which names comes from month and years), create single table, for example: Sales.
Each record should be marked with the timestamp, then you'll be able to get data from single table!

Try!

这篇关于如何动态地做联合运算符并获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！