从SQL表中删除空行 [英] Remove empty rows from SQL table

问题描述

大家好，我有一个有三列的数据表（表1）; id，Developments和Crops。



我需要编写一个可以修改我的Table1的SQL查询，如表2所示。

谢谢< br $> b $ b

表1

 ----------- --------------------- 
 id |发展|庄稼
 ------------------------------- 
 0001 House 
 0001 Farm 
 0001芒果
 0001橙色
 0001 Neem Tree 
 0001菠萝

表2

 ------------------------------ -  
 id |发展|庄稼
 --------------------------------- 
 0001 House芒果
 0001 Farm Orange 
 0001 Neem Tree 
 0001菠萝

我尝试过：



我试图并排使用数据表合并。

我首先创建一个包含所有开发的数据表，然后是另一个包含数据表的数据表所有作物。



然后合并两个数据表。这个问题是crop作者列中的行不能保存对id列的引用

解决方案

为了得到你想要的结果你将需要修改您的架构。你说你有一个名为Development的表和一个名为Crops的表。您的发展与作物有一对多的关系，您的作物与发展有很多关系。对我和我理解这个问题的方式，你的架构并没有多大意义。如果我有误解，请随意详细说明。



我要假设因为表2中Farm下面的行是空白的，它们意味着包含Farm。 br />


此代码应该在sql server中运行。

  DECLARE   @ Developments   TABLE  
（
 Id  INT   IDENTITY （ 1 ，  1 ） NOT   NULL   PRIMARY   KEY ，
开发 VARCHAR （ 200 ） NULL  
）
 
  DECLARE   @ Crops  表 
（
 Id  INT   IDENTITY  （ 1 ， 1 ） NOT   NULL   PRIMARY   KEY ，
 DevelopmentId < span class =code-keyword> INT   NOT   NULL ，
裁剪 VARCHAR （ 200 ） NULL  
）
 
  INSERT   INTO   @Developments （开发） VALUES （'  House'）
  INSERT   INTO   @ Developments （开发） VALUES （' 农场）
 
  INSERT   INTO   @ Crops （DevelopmentId，Crop） VALUES （ 1  ，' 芒果'）
  INSERT   INTO   @ Crops （DevelopmentId，Crop） VALUES （ 2 ，'  Orange'）
  INSERT   INTO   @ Crops （DevelopmentId，Crop） VALUES （ 2 ，' 印度楝树'）
  INSERT   INTO   @ Crops （DevelopmentId，Crop） VALUES （ 2 ，'  Pineapple '）
 
 
  SELECT  
 A.Developments，B.Crop 
  FROM   @ Developments   AS  A 
 < span class =code-keyword> JOIN   @ Crops   AS  B  ON  B.DevelopmentId = A.Id 

输出



开发作物

众议院芒果

农场橙色

农场印度楝树

农场菠萝

Hi everyone, i have a datatable (Table1) with three columns; id, Developments and Crops.

I need to write an sql query that can modify my Table1 to appear as in Table2.
Thanks

Table1

--------------------------------
id    | Developments	| Crops
-------------------------------
0001	House	
0001	Farm	
0001		          Mangoes
0001		          Orange
0001		          Neem Tree
0001		          Pineapple

Table2

--------------------------------
id    | Developments	| Crops
---------------------------------
0001	House	        Mangoes
0001	Farm	        Orange
0001		            Neem Tree
0001		            Pineapple

What I have tried:

I have tried to use data table merging side by side.
I first create a datatable containing all the Development and then another datatable containing all the crops.

I then merge the two datatables. The problem with this is that the row in crops column do not hold reference to the id column

解决方案

In order to get the results you are wanting you are going to need to modify your schema. You say you have 1 table called Development and 1 table called Crops. Your Developments have a one to many relationship to crops and your crops have a many to one relationship to developments. To me and the way i understand this question, your schema doesn't make a whole lot of sense. Feel free to elaborate if im misunderstanding.

Im going to make an assumption that because the rows below Farm in table 2 are blank, they are meant to contain Farm.

This code should run in sql server.

DECLARE @Developments TABLE 
(
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Developments VARCHAR(200) NULL
)

DECLARE @Crops TABLE 
(
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	DevelopmentId INT NOT NULL,
	Crop VARCHAR(200) NULL
)

INSERT INTO @Developments (Developments ) VALUES ('House')
INSERT INTO @Developments (Developments ) VALUES ('Farm')

INSERT INTO @Crops (DevelopmentId, Crop) VALUES (1, 'Mangoes')
INSERT INTO @Crops (DevelopmentId, Crop) VALUES (2, 'Orange')
INSERT INTO @Crops (DevelopmentId, Crop) VALUES (2, 'Neem Tree')
INSERT INTO @Crops (DevelopmentId, Crop) VALUES (2, 'Pineapple')


SELECT 
	A.Developments, B.Crop
FROM @Developments AS A 
JOIN @Crops AS B ON B.DevelopmentId = A.Id

Output

Developments Crop
House Mangoes
Farm Orange
Farm Neem Tree
Farm Pineapple

这篇关于从SQL表中删除空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！