Tänään tuli vastaan tapaus jossa piti hakea tietokannasta SQL-kysellä
kaikkein yleisimmin esiintyvä arvo tietyssä taulun sarakkeessa.
Ei ollut varmaankaan aikaisemmin ollut tarvetta moiselle kyselylle,
mutta lopulta googlen kautta vastauksen löysin.
E' un'applicazione di interfaccia con Sqlite, per l'amministrazione del db. Per chi non lo conoscesse, Sqlite è un database di "tipo" sql (utilizza pressapoco la stessa sintassi) molto utilizzato nelle applicazioni che necessitano di avere un database piccolo e portabile.
Typically you would use a JOIN clause when querying from multiple tables, but no one said you have to. Say for example you wanted to query two tables (tableA & tableB) which did not have any relationship (no one-to-one at all), yet there are fields in which would permit a comparison. Say street number, pre-direction, street name and the suffix.
Well you would compare
(tableA.street number = tableB.street number) and
(tableA.pre-direction = tableB.pre-direction) and
(tableA.street name = tableB.street name) and
(tableA.suffix = tableB.suffix).
This first example will illustrate a query using a JOIN clause.
Results
This example illustrates a query without using a JOIN clause.
Results
As you can see the results are the same, but the queries are a tad different.
-- samson
Documentare la base dati con GraphViz. Un altro strumento che utilizza la descrizione DOT dei grafici.
In this particular scenario I needed to concatenate a set of fields into a single column. So essentially I needed to trim all the leading zeros from the street number field first (via my udf_TrimLeadingZeros), then pieced the street number, pre-direction, street name and suffix all into one field. However, every row did not have a pre-direction or a suffix; nonetheless, I needed everything formatted (evenly spaced between words) and displayed as a single string.
So I figured I would use a CASE expression (http://bit.ly/PiYQF) in conjunction with a String Concatenation (http://bit.ly/FJtq9) to achieve my end result. Typically I see a period (.) appended to the pre-direction, but in this case it wasn't required. However if you do need it simply add a period to line 14 between the ticks '. ' of the attached select script (see pre-direction with periods screen shot for details).
Pre-Direction without periods
Pre-Direction with periods
There are a lot of sites out there that give an in-depth definition of identity columns (this ain't one of them), but I have to admit that it was something I took for granted recently. In my task to clean up geospatial address data, specifically for geo-coding purposes I over looked the goodness of having an identity field as part of table. The data that I received did not have an ID field and for some odd reason I thought nothing of. I figured I would make updates by comparing multiple columns to target specific rows. Well that worked fine until I realized that I had duplicate data. Digging myself deeper into a whole I kept doing things the hard way, well let's just say I did things the less efficient way.
Once I finally realized what I had been doing was inefficient I decided to introduce an ID field to the table. However old habits are hard to kill. Typically when I create ID fields I always leave (identity) off because I use UDFs to generate my sequential numbering. Reason being if there is ever a need to remove a record or delete a record (which seldom occurs since I use bit fields to designate active versus inactive records) the numbering continues. So if I have rows one through five and physically delete row five (ID 5), my next insert will become ID 6. Making the sequence 1,2,3,4,6.
Some may disagree but I really don't like that and no I am not OCD. It's just that I hate wasting IDs and avoid it whenever possible, hence my dilemma. So I add the column via the UI and save my changes. Then I start to think what is the best way to populate this newly added column. Not seeing the obvious (big rookie mistake on my part) I must have wasted 30 mins to an hour before I stepped away to grab a drink. Then all of a sudden it occurred to me that I could drop the column and re-add it via alter statement and all my worries would be gone. Which I did. You can do the same using the UI.
One simple single line could have prevented a self inflicted heartache because I was being an idiot. Once I ran it, the row was created and populated in sequential order. Just goes to show sometimes you need to step away from the task at hand to see what you are doing wrong! Granted I only needed the column temporarily; nonetheless, it helped a great deal when running bulk updates and having to compare a single column versus five to six. Especially when dealing with hundreds of thousands of records.
ALTER STATEMENT:
ALTER TABLE TableName ADD ID INT IDENTITY(1,1)
Just to give you a visual of what exactly transpired here is little step by step (example data only):
BEFORE AND AFTER
1) SELECT * FROM TestTable
2) ALTER TABLE TestTable ADD ID INT IDENTITY(1,1)
3) SELECT * FROM TestTable
There are lot's of sites (paid and free) that try to answer our questions in many domains. HowTo and DIY and stuff...
|
After the confirmed success stackoverflow that answers programmers questions, the real news is that there is a link at the bottom... to another site that I hope gets filled with good answers and it's a twin site called doctype and is aimed at... as they put it: |
Doctype is a free question and answer site for web designers. You can ask questions about CSS, HTML, web design and email design.
Check the whole family
I work a great deal with SQL Server Reporting Services and in some (if not most) of my previous reports I often needed to calculate the number of days between two given dates. Usually the amount of days a Work Order has been open, how many days it's past due and such. Well using the DateDiff() function it makes it really simple to accomplish. However If you need to calculate age there is a bit more logic required. See Jacob's post at http://bit.ly/2O2qWW for further deatils.
Attached in the [DOWNLOAD SECTION] is a script titled "FourPartStringParse_v0.01.sql" that will parse out an address. It has limitations though. See the file comments for details. Thanks to @MarlonRibunal for the help!
I tried this route, but the problem with this script is that it counts every space. So if there happens to be a double space between two words then it gets added to the total count.
DECLARE @String NVARCHAR(100)
SET @String = 'How many words are there in this big string?'
SELECT @String,(1 + LEN(@String) - LEN(REPLACE(@String, ' ', ''))) AS WordCount as you can see in the illustration below.
The following method worked better, but I still had some issues with it as I mentioned before.
